BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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  .

1 ACCEPTED SOLUTION

Accepted Solutions
jenim514
Pyrite | Level 9

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

7 REPLIES 7
stat_sas
Ammonite | Level 13

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
PROC Star

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
Pyrite | Level 9

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
PROC Star

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

 

do _n_=1 to dim(crgs);

jenim514
Pyrite | Level 9

@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
PROC Star

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
Pyrite | Level 9
@Astounding Worked great! Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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