proc tranpose with multiple var variables

[vraj1]

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;

[Kurt_Bremser]

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.

[vraj1]

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.

[Kurt_Bremser]

I hate to repeat myself, but:

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

[vraj1]

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

[Astounding]

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.

[vraj1]

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

[Kurt_Bremser]

---

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

[vraj1]

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

[Kurt_Bremser · Accepted solution]

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;

[mj5]

Hi, Can this be done using arrays?

[Kurt_Bremser]

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.