topic Re: proc transpose long in SAS Programming

proc transpose long

jenim514 — Wed, 20 Apr 2016 16:35:19 GMT

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 .

Re: proc transpose long

stat_sas — Wed, 20 Apr 2016 17:01:49 GMT

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;

Re: proc transpose long

Astounding — Wed, 20 Apr 2016 17:09:02 GMT

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;

if already_output=' ' then output;

run;

Re: proc transpose long

jenim514 — Wed, 20 Apr 2016 17:25:31 GMT

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!

Re: proc transpose long

Astounding — Wed, 20 Apr 2016 17:27:29 GMT

When using _n_ as the index to the array, the DO loop has to match.

do _n_=1 to dim(crgs);

Re: proc transpose long

jenim514 — Wed, 20 Apr 2016 17:39:03 GMT

@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

Re: proc transpose long

Astounding — Wed, 20 Apr 2016 17:56:22 GMT

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.

Re: proc transpose long

jenim514 — Wed, 20 Apr 2016 21:18:28 GMT

@Astounding Worked great! Thank you!