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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.