Transposing data

[starz4ever2007]

I am trying to transpose data that looks like

subjid  drug  week   q1  q2   q3   q4 

001      A        1        4     3     3     0   

001      A        8        2    2      3     0    

001      A        12      2    2      3     0 

001      A        16      2    2      3     0 

002      B        1        2    1      3     0 

002      B        8        2    3      3     0 

002      B        12      1    2      3     0 

002      B        16      2    2      3     0 

003      A        1        2    2      3     0 

003      A        8        2    2      3     0 

I am trying to transpose from long to wide so that it reads something like this:

subjid  drug    wk1q1  wk1q2   wk1q3  wk1q4  wk8q1  wk8q2 wk8q3....all the way to wk16q4

001      A             4           3          3           0            2          2      3    

002      B             2           1          3           0            8          2      3     

003      A             1           2          2           3            0          8      2    

[PGStats · Accepted solution]

Your data in not in a long structure. Reshape it to long, then make it extra wide:

data have;
input subjid  drug $ week   q1  q2   q3   q4;
datalines;
001      A        1        4     3     3     0   
001      A        8        2    2      3     0    
001      A        12      2    2      3     0 
001      A        16      2    2      3     0 
002      B        1        2    1      3     0 
002      B        8        2    3      3     0 
002      B        12      1    2      3     0 
002      B        16      2    2      3     0 
003      A        1        2    2      3     0 
003      A        8        2    2      3     0 
;

proc transpose data=have out=long name=q prefix=value;
by subjid drug week;
var q:;
run;

proc transpose data=long out=extraWide(drop=_NAME_) prefix=wk;
by subjid drug;
id week q;
var value1;
run;

As a rule, this extra wide structure will be more cumbersome to process with SAS tools than a true long structure would.

[Ksharp]

If you have big table, Check the MERGE skill proposed by Me,Matt, Arthur.T :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf





data work.have;
input subjid $ drug $ week   q1  q2   q3   q4 ;
cards;
001      A        1        4     3     3     0   
001      A        8        2    2      3     0    
001      A        12      2    2      3     0 
001      A        16      2    2      3     0 
002      B        1        2    1      3     0 
002      B        8        2    3      3     0 
002      B        12      1    2      3     0 
002      B        16      2    2      3     0 
003      A        1        2    2      3     0 
003      A        8        2    2      3     0 
;
run;
data temp(index=(x=(subjid drug)));
 set have;
 by subjid drug;
 if first.drug then n=0;
 n+1;
run;
proc sql;
select distinct catt('temp(where=(n=',n,')
 rename=(q1-q4=wk',week,'q1-wk',week,'q4))') 
  into : list separated by ' '
   from temp;
quit;
data want;
 merge &list;
 by subjid drug;
 drop n week;
run;

[Ksharp]

Sorry. Last code wouldn't work if there are missing levels in a group, 
Try this one :





data work.have;
	length STATE $ 2 YEAR 8 RACE $ 1 F_0-F_10 8;	
	input State Year Race F_0-F_10;
	datalines;
CA 2010 W 10 11 12 13 14 15 16 17 18 19 20
CA 2010 B 30 31 32 33 34 35 36 37 38 39 40
CA 2010 H 50 51 52 53 54 55 56 57 58 59 60
CA 2011 W 44 44 44 44 44 44 44 44 44 44 44
CA 2011 B 22 22 22 22 22 22 22 22 22 22 22
CA 2011 H 88 88 88 88 88 88 88 88 88 88 88
;
run;


proc sql;
select distinct catt('temp(where=(state="',state,'" and year=',year,')
 rename=(F_0-F_10=',race,'_F_0-',race,'_F_10))') 
  into : list separated by ' '
   from temp;
quit;
data want;
 merge &list;
 by State Year;
 drop n race;
run;

[Ksharp]

Opps.



data work.have;
input subjid $ drug $ week   q1  q2   q3   q4 ;
cards;
001      A        1        4     3     3     0   
001      A        8        2    2      3     0    
001      A        12      2    2      3     0 
001      A        16      2    2      3     0 
002      B        1        2    1      3     0 
002      B        8        2    3      3     0 
002      B        12      1    2      3     0 
002      B        16      2    2      3     0 
003      A        1        2    2      3     0 
003      A        8        2    2      3     0 
;
run;

proc sql;
select distinct catt('have(where=(week=',week,')
 rename=(q1-q4=wk',week,'q1-wk',week,'q4))') 
  into : list separated by ' '
   from have;
quit;
data want;
 merge &list;
 by subjid drug;
 drop  week;
run;