A simple transpose with multiple variables with a prefix, or different output to proc univariate

[eh51]

I have this code to create a table of percentiles. It outputs a wide table. But imho a tall table would be easier to read, and it will fit better on an excel sheet with a SAS box/whiskers plot. 

I looked on this forum and elsewhere on how to transpose across multiple columns with a common prefix, and maybe this is real simple, but I have not found a solution. 

Another way to skin this cat would be to have proc univariate output a tall table instead of a wide table. 

Any recommendations are appreciated.  

proc univariate data=work.w_run;
var w_avg;
by mycat;
output out=w_avg_out
pctlpts = 00, 01, 10, 25, 50, 75, 90, 99, 100
pctlpre = P_;
run;

data have:

Obs	mycat	P_0	P_1	P_10	P_25	P_50	P_75	P_90	P_99	P_100
1	No	-3.47517	-3.20683	8.96184	86.291	110.444	116.45	126.381	133.746	137.535
2	Yes	-4.86	-3.308	0.19083	5.6582	15.096	62.953	110.816	128.516	129.165

data want:

mycat	No	Yes
P_0	-3.47517	-4.86
P_1	-3.20683	-3.308
P_10	8.96184	0.19083
P_25	86.291	5.6582
P_50	110.444	15.096
P_75	116.45	62.953
P_90	126.381	110.816
P_99	133.746	128.516
P_100	137.535	129.165

[yabwon · Accepted solution]

So do the transposition:

data have;
  input Obs	mycat $	P_0	P_1	P_10	P_25	P_50	P_75	P_90	P_99	P_100;
cards;
1	No	-3.47517	-3.20683	8.96184	86.291	110.444	116.45	126.381	133.746	137.535
2	Yes	-4.86	-3.308	0.19083	5.6582	15.096	62.953	110.816	128.516	129.165
;
run;
proc print;
run;

proc transpose data = have out = want(drop =_NAME_);
  var P_:;
  id mycat;
run;
proc print;
run;

Bart

[Reeza]

proc transpose data=w_avg_out out=want;
id mycat;
run;

Can be as simple as above, if the VAR statement is excluded all numeric values are included.

[ballardw]

It might be easier/cleaner in the long run to use a different procedure to do the display and calculation.

Consider this:

proc tabulate data=sashelp.class;
  class sex ;
  var weight;
  table weight*(min P1 p10 p25 p50 p75 p95 p99 max),
        sex 
  ;
run;

At least I think P0 is Min and P100 is max.

One advantage of a report procedure like tabulate is besides controlling layout you can do things like getting the overall distribution in one table by adding ONE WORD:

proc tabulate data=sashelp.class;
  class sex ;
  var weight;
  table weight*(min P1 p10 p25 p50 p75 p95 p99 max),
        sex All
  ;
run;

If you want separate similar tables for multiple variables

proc tabulate data=sashelp.class;
  class sex age;
  var weight height;
  table weight*(min P1 p10 p25 p50 p75 p95 p99 max),
        ALL sex ;
  ;
  table height*(min P1 p10 p25 p50 p75 p95 p99 max),
        ALL sex ;
  ;
  table weight*(min P1 p10 p25 p50 p75 p95 p99 max),
        ALL Age ;
  ;
   table height*(min P1 p10 p25 p50 p75 p95 p99 max),
        ALL Age ;
  ;
run;

You could even "stack" the summarized variables into one table:

proc tabulate data=sashelp.class;
  class sex age;
  var weight height;
  table (weight height)*(min P1 p10 p25 p50 p75 p95 p99 max),
        sex ;
  ;

run;

You can use a Keylabel to assign different text than the default for the statistics.

This actually just scratches the surface of what you do with this report procedure.

[eh51]

I looked at proc tabulate, but the output data sets are also wide, though the display is tall. Thanks for the recommendation though. 

[data_null__]

This might meet your needs.

proc stdize data=sashelp.class outstat=want out=_null_
   pctlpts = 00, 01, 10, 25, 50, 75, 90, 99, 100;
   run;

[eh51]

This is a cool procedure. I got a warning that it is expiring though.  Thanks for the recommendation. 

[data_null__]

---

@eh51 wrote:

This is a cool procedure. I got a warning that it is expiring though.  Thanks for the recommendation. 

---

PROC STDIZE is a SAS/STAT procedure.  The message about expiring soon is related to the license for SAS/STAT.  It is a bit odd to me that you don't also get a message about BASE SAS expiring.  I have "never" seen an installation were the various products expiration was not all the same date.

Time to pay the bill. 💣