topic Re: Transposing data in SAS Programming

Transposing data

starz4ever2007 — Wed, 27 Jul 2016 20:29:30 GMT

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

Re: Transposing data

PGStats — Wed, 27 Jul 2016 21:54:08 GMT

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.

Re: Transposing data

Ksharp — Thu, 28 Jul 2016 02:49:23 GMT

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;

Re: Transposing data

Ksharp — Thu, 28 Jul 2016 07:43:21 GMT

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;

Re: Transposing data

Ksharp — Thu, 28 Jul 2016 07:46:25 GMT

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;