Can someone assist with proc transpose?
My transpose attempt:
proc transpose data=CRG.ASTHMA_FY10to14_F out=long prefix=CRG;
by patientid;
var concurrent_fy10-concurrent_fy14;
run;
resulted in this (patientid hidden by me):
Obs | _NAME_ | CRG1 | CRG2 | CRG3 | CRG4 | CRG5 |
---|---|---|---|---|---|---|
1 | CONCURRENT_FY10 | 10000 | 10000 | |||
2 | CONCURRENT_FY11 | 51381 | 51381 | |||
3 | CONCURRENT_FY12 | 51381 | ||||
4 | CONCURRENT_FY13 | |||||
5 | CONCURRENT_FY14 | |||||
How can I get to this type of table. I can do all the renaming in the data step, but just need help getting to this format.
ID YEAR CRG
1 2010 10000
1 2010 10000
1 2011 51381
1 2011 51381
1 2012 51381
1 2013 .
1 2014 .
The example i sent is just a small representaion of all the CRGs ...I have 517 CRG colums. So I did this syntax:
data long_want;
set long;
array crgs(*) crg:;
do i=1 to dim(crgs);
if crgs {_n_}> . then do;
crg=crgs{_n_};
output;
already_output='Y';
end;
end;
if already_output='' then output;
run;
And go an error: ERROR: Array subscript out of range at line 272 column 8. This may be because of all the columns? Is there a way to modify the syntax a bit?
Thanks!
If you are okay with data step then this may provide the desired output.
data want(where=(CRG ne .));
set have;
array cr(*) cr:;
do i=1 to dim(cr);
CRG=cr(i);
output;
end;
keep obs _name_ crg;
run;
A slight variation might be needed, to guarantee an observation for each year:
data want;
set have;
array crgs {5} crg1-crg5;
do _n_=1 to 5;
if crgs{_n_} > . then do;
crg=crgs{_n_};
output;
already_output='Y';
end;
end;
if already_output=' ' then output;
run;
The example i sent is just a small representaion of all the CRGs ...I have 517 CRG colums. So I did this syntax:
data long_want;
set long;
array crgs(*) crg:;
do i=1 to dim(crgs);
if crgs {_n_}> . then do;
crg=crgs{_n_};
output;
already_output='Y';
end;
end;
if already_output='' then output;
run;
And go an error: ERROR: Array subscript out of range at line 272 column 8. This may be because of all the columns? Is there a way to modify the syntax a bit?
Thanks!
When using _n_ as the index to the array, the DO loop has to match.
do _n_=1 to dim(crgs);
@Astounding I am still getting wide output. it looks like the wide is duplicating long?
Obs | _NAME_ | CRG1 | CRG2 | CRG3 | CRG4 | CRG5 | CRG6 | CRG7 | CRG8 | CRG9 | already_output |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | CONCURRENT_FY10 | 10000 | 10000 | ||||||||
2 | CONCURRENT_FY10 | 10000 | 10000 | Y | |||||||
3 | CONCURRENT_FY11 | 51381 | 51381 | ||||||||
4 | CONCURRENT_FY11 | 51381 | 51381 | Y | |||||||
5 | CONCURRENT_FY12 | 51381 | 51381 | 51381 | |||||||
6 | CONCURRENT_FY12 | 51381 | 51381 | 51381 | Y | ||||||
7 | CONCURRENT_FY12 | 51381 | 51381 | 51381 | Y | ||||||
8 | CONCURRENT_FY13 | 10000 | |||||||||
9 | CONCURRENT_FY14 | 10000 |
Looks like you need to add this:
drop crg1-crg517 already_output;
Don't do this:
drop crg:;
That will also get rid of CRG.
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.