Hello,
I try to modify my 'Have' data to 'Want'.
I tried and it looks like I need to do subset and then merge, or transpose then subset and merge.
Is it a smart way to do it all at once? If you were me, what will you do?
data have; input group $ order L1 L2 ; datalines; A1 1 45 36 A1 2 25 55 A2 1 88 99 A2 2 74 78 A3 1 4 . 25 A3 2 88 99 B4 1 78 65 B4 2 . . ; run;
This question gets asked a lot. Using PROC transpose you will need to transpose to "full-tall" then flatten.
data have;
input group $ order L1 L2;
datalines;
A1 1 45 36
A1 2 25 55
A2 1 88 99
A2 2 74 78
A3 1 4 . 25
A3 2 88 99
B4 1 78 65
B4 2 . .
;
run;
proc print;
run;
proc transpose data=have out=tall;
by group order;
var L:;
run;
proc sort;
by group _name_ order; /*your variable order*/
run;
proc print;
run;
proc transpose data=tall out=wider(drop=_name_) delim=_;
by group;
var col1;
id _name_ order;
run;
proc print;
run;
Wide data sets are difficult to work with, compared to the same data in a long data set. So without a very good reason why you need this wide data set, I would say don't bother.
Instead of concentrating on the technical problem of transposing, better would be if you explain the problem from a big picture point of view; and explain what you will do next after you transpose.
This question gets asked a lot. Using PROC transpose you will need to transpose to "full-tall" then flatten.
data have;
input group $ order L1 L2;
datalines;
A1 1 45 36
A1 2 25 55
A2 1 88 99
A2 2 74 78
A3 1 4 . 25
A3 2 88 99
B4 1 78 65
B4 2 . .
;
run;
proc print;
run;
proc transpose data=have out=tall;
by group order;
var L:;
run;
proc sort;
by group _name_ order; /*your variable order*/
run;
proc print;
run;
proc transpose data=tall out=wider(drop=_name_) delim=_;
by group;
var col1;
id _name_ order;
run;
proc print;
run;
data test;
input group $ order L1 L2;
datalines;
A1 1 45 36
A1 2 25 55
A2 1 88 99
A2 2 74 78
A3 1 4 . 25
A3 2 88 99
B4 1 78 65
B4 2 . .
;
run;
proc transpose data=test out=test_L1 prefix=L1_;
by group;
var L1;
id order;
run;
proc transpose data=test out=test_L2 prefix=L2_;
by group;
var L2;
id order;
run;
data test_m;
merge test_L1 test_L2;
by group;
keep group L:;
run;
proc print data=test_m;
run;
data have;
input group $ order L1 L2 ;
datalines;
A1 1 45 36
A1 2 25 55
A2 1 88 99
A2 2 74 78
A3 1 4 . 25
A3 2 88 99
B4 1 78 65
B4 2 . .
;
run;
proc sql noprint;
select max(n) into :n from (select count(*) as n from have group by group);
quit;
proc summary data=have nway;
class group;
output out=want idgroup(out[&n.] (L1 L2)=);
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.