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.
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;
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?
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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.