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
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.
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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.