topic Merge multiple rows into a single row by group in SAS Programming

Merge multiple rows into a single row by group

liyongkai800 — Thu, 07 Mar 2019 22:27:09 GMT

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.

Re: Merge multiple rows into a single row by group

Astounding — Thu, 07 Mar 2019 22:37:28 GMT

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?

Re: Merge multiple rows into a single row by group

novinosrin — Thu, 07 Mar 2019 22:40:44 GMT


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;

Re: Merge multiple rows into a single row by group

novinosrin — Thu, 07 Mar 2019 22:42:56 GMT


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;

Re: Merge multiple rows into a single row by group

DanielLangley — Thu, 07 Mar 2019 22:46:52 GMT

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;

Re: Merge multiple rows into a single row by group

DanielLangley — Fri, 08 Mar 2019 07:08:47 GMT

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

Re: Merge multiple rows into a single row by group

Astounding — Fri, 08 Mar 2019 07:40:20 GMT

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

Re: Merge multiple rows into a single row by group

liyongkai800 — Fri, 08 Mar 2019 09:54:59 GMT

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

Re: Merge multiple rows into a single row by group

Astounding — Fri, 08 Mar 2019 14:42:14 GMT

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.