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.
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.