DATA Step, Macro, Functions and more

how to transpose data to get output

Reply
Super Contributor
Posts: 272

how to transpose data to get output

Dear,

 

I need help in my code to get the output. 

Some one helped me yesterday. But I did not get the out put I need exactly.

I need to transpose data by variables visit,test with ID=trt. Please help. Thank you

 

data one;
input id trt $ visit $ test $ bas val;
datalines;
1 a wk1 pul 2 3
2 b wk1 pul 3 6
3 c wk1 pul 4 9
4 a wk1 pul 3 7
5 b wk1 pul 3 7
6 c wk1 pul 3 7
1 a wk1 bp 10 12
2 b wk1 bp 12 14
3 c wk1 bp 14 16
4 a wk1 bp 12 14
5 b wk1 bp 14 16
6 c wk1 bp 16 18
1 a wk2 pul 2 3
2 b wk2 pul 3 6
3 c wk2 pul 4 9
4 a wk2 pul 3 7
5 b wk2 pul 3 7
6 c wk2 pul 3 7
1 a wk2 bp 10 12
2 b wk2 bp 12 14
3 c wk2 bp 14 16
4 a wk2 bp 12 14
5 b wk2 bp 14 16
6 c wk2 bp 16 18
;

 

proc means data=one stackodsoutput mean std median min max nway;
class test visi trt;
var v;
ods output summary=two;
run;

 

proc sort data=two;
by test visit trt;
run;

 

proc transpose data=two out=three(drop=_name_ );
by test visit ;
var mean median min max;
id trt; 
run;

 

output needed: 

 

visi        stat       a               b                     c                 test

 

 wk1     n           2               2                     2                  pul

 wk1    mean     5(STD)       6.5(STD)           8(STD)          pul

 wk1    median    5              6.5                   8                  pul

 wk1   range      min,max     min,max           min,max         pul

 wk2     n           2               2                     2                   pul

 wk2    mean     5  (STD)     6.5 (STD)          8 (STD)          pul

 wkw   median    5              6.5                   8                  pul

 wk2   range      min,max     min,max           min,max        pul

 

 

PROC Star
Posts: 7,474

Re: how to transpose data to get output

Posted in reply to knveraraju91

I think that the following does what you want:

 

proc means data=one stackodsoutput mean std median min max nway;
  class test visit trt;
  var bas val;
  ods output summary=two;
run;

data two (drop=StdDev nobs _median min max _:);
  set two (rename=(mean=_mean median=_median));
  mean=catt(put(_mean,3.1),' (',put(StdDev,6.4),')');
  Range=  catt(put(min,3.1),',', put(max,3.1)) ;
  n=put(nobs,3.);
  median=put(_median,3.);
run;  
proc transpose data=two out=three;
  var n median mean range;
  by test visit trt;
run;

proc sort data=three;
  by test visit _name_ trt;
run;
 
proc transpose data=three out=four;
  by test visit _name_;
  var col1;
  id trt; 
run;

Art, CEO, AnalystFinder.com

 

Super User
Super User
Posts: 7,955

Re: how to transpose data to get output

Posted in reply to knveraraju91

You appear to have the code nearly there, what is it you cannot do?  You will just need a datastep which process the means output into the format you want, note that variables a b and c in teh output dataset would need to be character and the numerics from means put to character values as min, max is not a numeric value.  As for actually doing this for you, this is not a contract board just a Q&A.  

PROC Star
Posts: 307

Re: how to transpose data to get output

Posted in reply to knveraraju91

You have a number of issues with this code, starting with typos (e.g. "visi" for "visit,", etc.). Once you resolve those, the main complication is really your creation of the range value, which will likely result in mixed types in your transpose. How strict is your requirement to have the range rather than the min and max fields in the output?

Ask a Question
Discussion stats
  • 3 replies
  • 136 views
  • 3 likes
  • 4 in conversation