Merge multiple rows into a single row by group

[liyongkai800]

Say I have a dataset as following:

group  id  var1  var2

   1      1    x1     x2 

   1      2    x3     x4

   1      3    x5     x6

   2      1    y1     y2

   2      2    y3     y4

   2      3    y5     y6

   3      1    z1     z2

   .       .      .       .

   .       .      .       .

I want to obtain a dataset like this:

group       var1    var2     var3     var4    var5    var6

    1           x1        x2        x3         x4      x5        x6

    2           y1        y2        y3         y4      y5        y6

    3           z1        z2        .... 

   ...

Any idea on how to transpose the 1st table? Thanks.

[Astounding]

Does your ID variable really take on values of 1, 2, and 3 all the time?  That would make life simple if true.

Are VAR1 and VAR2 character variables?

[liyongkai800]

yes, ID is always like 1,2,3. Var1 and Var2 are numerical variables

[Astounding]

In that case, I would use the @DanielLangley solution (with a couple of patches to it ... retain the new variables, and clean them out when beginning a new GROUP just in case that GROUP doesn't have observations for all 3 ID values).  That's the approach I would have used if I had received your answer sooner.

[novinosrin · Accepted solution]

data have;
input group  id  var1 $  var2 $;
cards;
   1      1    x1     x2 
   1      2    x3     x4
   1      3    x5     x6
   2      1    y1     y2
   2      2    y3     y4
   2      3    y5     y6
   3      1    z1     z2
   ;

proc transpose data=have out=_have(drop=_name_ id);
by group id ;
var var:;
run;
proc sql;
select max(c) into :m
from (select group, count(col1) as c from _have group by group);
quit;

data want;
do _n_=1 by 1until(last.group);
set _have;
by group;
array var(&m)$2;
var(_n_)=col1;
end;
drop col1;
run;

[novinosrin]

data have;
input group  id  var1 $  var2 $;
cards;
   1      1    x1     x2 
   1      2    x3     x4
   1      3    x5     x6
   2      1    y1     y2
   2      2    y3     y4
   2      3    y5     y6
   3      1    z1     z2
   ;

proc transpose data=have out=_have(drop=_name_ id);
by group id ;
var var:;
run;
proc sql;
select max(c) into :m
from (select group, count(col1) as c from _have group by group);
quit;


proc summary nway data=_have missing; 
 class group; 
 output 
 out = work.wide(drop=_type_ _freq_) 
 idgroup(out[&m](col1)=) 
 ;
run;

[DanielLangley]

I'm typing this on mobile and haven't ran it but something like ..

Data work.want(drop=id have1 have2);
Set work.have(rename = (var1 = have1 var2=have2));
By group;
Array vars{6} var1-var6;

Vars{(id*2)-1} = have1;
Vars{id*2} =have2;
If last.group;
Run;

[DanielLangley]

You would probably also need to retain these values as well. Oops.

[Astounding]

Yes, and clean out the retained values when beginning a new GROUP.