proc transpose long

[jenim514]

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  .

[stat_sas]

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;

[Astounding]

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;

[jenim514 · Accepted solution]

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!

[Astounding]

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

do _n_=1 to dim(crgs);

[jenim514]

@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

[Astounding]

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.

[jenim514]

@Astounding Worked great! Thank you!