CODEWITHSUNDEEP
sas
Shingston
Shingston

Reputation: 217

Add values of column2 to the right of a row by matching column1 values

I have a dataset where there are multiple subsequent entries with duplicate values of column1, and different values of column2.

Something like this :

column1 column2
1 1
1 2
1 3
2 1
2 2
2 3

My desired output is the following :

column1 column2_1 column2_2 column2_3
1 1 2 3
2 1 2 3

My current solution uses the RETAIN and BY group to concatenate the different values of column2, and then using scan() to separate them into their respective columns, that I lifted from HERE and HERE.

I was wondering, is there a more elegant (or maybe even better, inbuilt?) method of combining rows in this manner?

Thank you.

Upvotes: 0

Views: 47

Answers (1)

whymath
whymath

Reputation: 1394

It's proc transpose, definitely.

data have;
  input column1 column2;
  cards;
1   1
1   2
1   3
2   1
2   2
2   3
;
run;

proc transpose data=have out=want prefix=column2_;
  by column1;
  var column2;
run;

This procedure is designed to transpose data.
However, if you insist on doing this in data step. Array method is sort of elegant, I think.

data want;
  i=0;
  do until(last.column1);
    set have;
    i+1;
    array column2_[3];
    by column1;
    column2_[i]=column2;
  end;
run;

You may need to replace the number 3 by a macro variable to make this skill portable.

Upvotes: 1

Related Questions