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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: