topic how to transpose data to get output in SAS Programming

how to transpose data to get output

knveraraju91 — Tue, 28 Feb 2017 15:28:12 GMT

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

Re: how to transpose data to get output

art297 — Tue, 28 Feb 2017 16:39:56 GMT

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

Re: how to transpose data to get output

RW9 — Tue, 28 Feb 2017 16:41:08 GMT

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.

Re: how to transpose data to get output

collinelliot — Tue, 28 Feb 2017 16:42:58 GMT

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?