How to transpose data for muti cols and rows

[stataq]

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;

[PaigeMiller]

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.

[data_null__ · Accepted solution]

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;

[JOL]

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;

[Ksharp]

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;