DATA Step, Macro, Functions and more

proc transpose long

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

proc transpose long

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  .


Accepted Solutions
Solution
‎04-20-2016 05:18 PM
Frequent Contributor
Posts: 121

Re: proc transpose long

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!

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: proc transpose long

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;

Super User
Posts: 5,096

Re: proc transpose long

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;

Solution
‎04-20-2016 05:18 PM
Frequent Contributor
Posts: 121

Re: proc transpose long

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!

 

Super User
Posts: 5,096

Re: proc transpose long

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

 

do _n_=1 to dim(crgs);

Frequent Contributor
Posts: 121

Re: proc transpose long

@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  

 

Super User
Posts: 5,096

Re: proc transpose long

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.

Frequent Contributor
Posts: 121

Re: proc transpose long

@Astounding Worked great! Thank you!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 370 views
  • 5 likes
  • 3 in conversation