DATA Step, Macro, Functions and more

proc tranpose with multiple var variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 208
Accepted Solution

proc tranpose with multiple var variables

i have variable

dose   param       count             percent

1_yt     nhdi          23                     34

2_tr      sdg          21                      32

 

I need to transpose these such that dose will be in the header and percent and param will be as it is.

I am using the below code but i get percent in one obs and count in other obs.

 

Can any one help me what i am missing

 

proc transpose data=stab11 out=stab12 ; 
by param ;
var count percent;
id dose;
run;


Accepted Solutions
Solution
‎09-28-2017 03:29 AM
Super User
Posts: 9,886

Re: proc tranpose with multiple var variables

You can get almost there (the names are a little different) by running two tranposes and merging the results:

proc sort data=inc;
by param;
run;

proc transpose data=inc out=int1 (drop=_name_) prefix=count_;
by param;
id dose_fmt;
var count;
run;

proc transpose data=inc out=int2 (drop=_name_) prefix=percent_;
by param;
id dose_fmt;
var percent;
run;

data want;
merge int1 int2;
by param;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,886

Re: proc tranpose with multiple var variables

The proper way to present example data looks like this:

data have;
input dose $ param $ count percent;
cards;
1_yt nhdi 23 34
2_tr sdg 21 32
;
run;

as it saves the people supposed to help you a lot of typing.

Please show an example of your expected output, based on this example dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 208

Re: proc tranpose with multiple var variables

Posted in reply to KurtBremser

sorry below is my dataset

data WORK.INC;
infile datalines dsd truncover;
input dose_fmt:$50. PARAM:$200. COUNT:32. PERCENT:32.;
datalines4;
ADURN,base Tot Score Less Than Equal To 12,3,3.5
DE dfg,base Tot Score Less Than Equal To 12,112,29.6
TOTAL,base Tot Score Less Than Equal To 12,115,24.8
ADURN,No Active next,1,1.2
DE dfg,No Active next,15,4
TOTAL,No Active next,16,3.4
ADURN,Outpatient Status,2,2.4
DE dfg,Outpatient Status,6,1.6
TOTAL,Outpatient Status,8,1.7
ADURN,YMRS Tot Score Less Than Equal To 12,1,1.2
DE dfg,YMRS Tot Score Less Than Equal To 12,25,6.6
TOTAL,YMRS Tot Score Less Than Equal To 12,26,5.6
;;;;

run;

and i am using the belwo proc transpose

proc transpose data=inc out=stab12; 
by param ;
var count percent;
id dose_fmt;
run;

 

In this output i get percent on one row and count on another whereas i need them to be next to each other.

 

Super User
Posts: 9,886

Re: proc tranpose with multiple var variables

I hate to repeat myself, but:

 

Please show an example of your expected output, based on this example dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 208

Re: proc tranpose with multiple var variables

Posted in reply to KurtBremser

I need like below 

 

data WORK.FINAL;
infile datalines dsd truncover;
input dose_fmt:$6. PARAM:$36. COUNT:BEST. PERCENT:BEST.;
datalines4;
ADURN,base Tot Score Less Than Equal To 12,3,3.5
DE dfg,base Tot Score Less Than Equal To 12,112,29.6
TOTAL,base Tot Score Less Than Equal To 12,115,24.8
ADURN,No Active next,1,1.2
DE dfg,No Active next,15,4
TOTAL,No Active next,16,3.4
ADURN,Outpatient Status,2,2.4
DE dfg,Outpatient Status,6,1.6
TOTAL,Outpatient Status,8,1.7
ADURN,YMRS Tot Score Less Than Equal To 12,1,1.2
DE dfg,YMRS Tot Score Less Than Equal To 12,25,6.6
TOTAL,YMRS Tot Score Less Than Equal To 12,26,5.6
;;;;

Super User
Posts: 6,629

Re: proc tranpose with multiple var variables

Data sets don't have headers.  Reports have headers.

 

So I agree with @KurtBremser in that you need to illustrate what you want the result to be.

Regular Contributor
Posts: 208

Re: proc tranpose with multiple var variables

Posted in reply to Astounding

This is how my output should look alike

data WORK.FINAL;
infile datalines dsd truncover;
input dose_fmt:$6. PARAM:$36. COUNT:BEST. PERCENT:BEST.;
datalines4;
ADURN,base Tot Score Less Than Equal To 12,3,3.5
DE dfg,base Tot Score Less Than Equal To 12,112,29.6
TOTAL,base Tot Score Less Than Equal To 12,115,24.8
ADURN,No Active next,1,1.2
DE dfg,No Active next,15,4
TOTAL,No Active next,16,3.4
ADURN,Outpatient Status,2,2.4
DE dfg,Outpatient Status,6,1.6
TOTAL,Outpatient Status,8,1.7
ADURN,YMRS Tot Score Less Than Equal To 12,1,1.2
DE dfg,YMRS Tot Score Less Than Equal To 12,25,6.6
TOTAL,YMRS Tot Score Less Than Equal To 12,26,5.6
;;;;

Super User
Posts: 9,886

Re: proc tranpose with multiple var variables


vraj1 wrote:

This is how my output should look alike

data WORK.FINAL;
infile datalines dsd truncover;
input dose_fmt:$6. PARAM:$36. COUNT:BEST. PERCENT:BEST.;
datalines4;
ADURN,base Tot Score Less Than Equal To 12,3,3.5
DE dfg,base Tot Score Less Than Equal To 12,112,29.6
TOTAL,base Tot Score Less Than Equal To 12,115,24.8
ADURN,No Active next,1,1.2
DE dfg,No Active next,15,4
TOTAL,No Active next,16,3.4
ADURN,Outpatient Status,2,2.4
DE dfg,Outpatient Status,6,1.6
TOTAL,Outpatient Status,8,1.7
ADURN,YMRS Tot Score Less Than Equal To 12,1,1.2
DE dfg,YMRS Tot Score Less Than Equal To 12,25,6.6
TOTAL,YMRS Tot Score Less Than Equal To 12,26,5.6
;;;;


Sorry, but that's exactly your example data. No action needed to get that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 208

Re: proc tranpose with multiple var variables

Posted in reply to KurtBremser

Sorry for it. Here is the below correct one

 

data WORK.FINAL;
infile datalines dsd truncover;
input param:$36. adurn_count:BEST. adurn_percent:$3. de_dfg_count:BEST. de_dfg_percent:$4. total_count:BEST. total_percent:$4.;
datalines4;
base Tot Score Less Than Equal To 12,3,3.5,112,29.6,115,24.8
No Active next,1,1.2,15,4.0,16,3.4
Outpatient Status,2,2.4,6,1.6,8,1.7
YMRS Tot Score Less Than Equal To 12,1,1.2,25,6.6,26,5.6
;;;;

Solution
‎09-28-2017 03:29 AM
Super User
Posts: 9,886

Re: proc tranpose with multiple var variables

You can get almost there (the names are a little different) by running two tranposes and merging the results:

proc sort data=inc;
by param;
run;

proc transpose data=inc out=int1 (drop=_name_) prefix=count_;
by param;
id dose_fmt;
var count;
run;

proc transpose data=inc out=int2 (drop=_name_) prefix=percent_;
by param;
id dose_fmt;
var percent;
run;

data want;
merge int1 int2;
by param;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor mj5
Occasional Contributor
Posts: 18

Re: proc tranpose with multiple var variables

Posted in reply to KurtBremser

Hi, Can this be done using arrays?

Super User
Posts: 9,886

Re: proc tranpose with multiple var variables

[ Edited ]

Why would you want to do that? You would have to determine the array sizes from the number of distinct values for dose_fmt, and use quite a lot of macro programming and/or call executes to set the final datastep up. Proc transpose is much more flexible, as you have seen, and does most of that work on its own.

 

PS of course one can drive screws in with a hammer, but I much prefer to use a screwdriver. See Maxim 14.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 448 views
  • 0 likes
  • 4 in conversation