topic Re: proc tranpose with multiple var variables in SAS Programming

proc tranpose with multiple var variables

vraj1 — Wed, 27 Sep 2017 09:16:57 GMT

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;

Re: proc tranpose with multiple var variables

Kurt_Bremser — Wed, 27 Sep 2017 09:25:08 GMT

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.

Re: proc tranpose with multiple var variables

vraj1 — Wed, 27 Sep 2017 09:43:31 GMT

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.

Re: proc tranpose with multiple var variables

Kurt_Bremser — Wed, 27 Sep 2017 09:51:56 GMT

I hate to repeat myself, but:

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

Re: proc tranpose with multiple var variables

Astounding — Wed, 27 Sep 2017 10:21:33 GMT

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

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

Re: proc tranpose with multiple var variables

vraj1 — Wed, 27 Sep 2017 10:28:13 GMT

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
;;;;

Re: proc tranpose with multiple var variables

vraj1 — Wed, 27 Sep 2017 11:08:41 GMT

This is how my output should look alike

Re: proc tranpose with multiple var variables

Kurt_Bremser — Wed, 27 Sep 2017 11:26:32 GMT

@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.

Re: proc tranpose with multiple var variables

vraj1 — Wed, 27 Sep 2017 11:37:33 GMT

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
;;;;

Re: proc tranpose with multiple var variables

Kurt_Bremser — Wed, 27 Sep 2017 11:58:45 GMT

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;

Re: proc tranpose with multiple var variables

mj5 — Thu, 07 Dec 2017 09:27:56 GMT

Hi, Can this be done using arrays?

Re: proc tranpose with multiple var variables

Kurt_Bremser — Thu, 07 Dec 2017 10:25:33 GMT

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.