topic Re: Proc transpose help in SAS Programming

Proc transpose help

knveraraju91 — Tue, 21 Sep 2021 16:41:25 GMT

Dear,

I need suggestion in Proc transpose step. Please suggest to get the out put needed.

data one;
input trt$ aval$ nobs pct;
datalines;
a complete 10 20
a disconti 20 40
b complete 20 40
b disconti 10 20
;
output need;
  aval       a_nobs   b_nobs a_pct b_pct
 complete      10       20         20    40
 disconti        20       10           40   20

Re: Proc transpose help

Reeza — Tue, 21 Sep 2021 16:53:42 GMT

And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

You need a double transpose. Do it first to get NOBS and PCT into a single row and then a second transpose to get it wide, using both TRT + variable_name (NOBS/TRT) as your ID to get them together.

FYI - if you calculated this from PROC MEANS you may be able to modify that proc to do this in that same step instead of doing it after the fact.

Re: Proc transpose help

Jagadishkatam — Tue, 21 Sep 2021 16:58:26 GMT

data one;
input trt$ aval$ nobs pct;
datalines;
a complete 10 20
a disconti 20 40
b complete 20 40
b disconti 10 20
;

proc sort data=one;
by aval;
run;

proc transpose data=one out=want suffix=_nobs;
by  aval;
id trt;
var nobs;
run;

proc transpose data=one out=want2 suffix=_pct;
by  aval;
id trt;
var pct;
run;

data want3;
merge want(in=a drop=_name_) want2( drop=_name_);
by aval;
run;

Re: Proc transpose help

Kurt_Bremser — Wed, 22 Sep 2021 10:11:32 GMT

If you want a report, consider this:

proc sort data=one;
by aval trt;
run;

proc transpose data=one out=trans;
by aval;
id trt;
var nobs pct;
run;

proc report data=trans;
column aval _name_,(a b);
define aval / group;
define _name_ / "" across;
define a / sum;
define b / sum;
run;

Re: Proc transpose help

Ksharp — Wed, 22 Sep 2021 12:59:47 GMT

data one;
input trt$ aval$ nobs pct;
datalines;
a complete 10 20
a disconti 20 40
b complete 20 40
b disconti 10 20
;

proc transpose data=one out=temp(index=(aval));
by trt aval;
var nobs pct;
run;

proc transpose data=temp out=want(drop=_name_) delimiter=_;
by aval;
var col1;
id trt _name_;
run;