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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User

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
Quartz | Level 8

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.

 

vraj1
Quartz | Level 8

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

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
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

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
Super User

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
Obsidian | Level 7 mj5
Obsidian | Level 7

Hi, Can this be done using arrays?

Kurt_Bremser
Super User

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.

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
  • 11 replies
  • 1338 views
  • 0 likes
  • 4 in conversation