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
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
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
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.
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.
I looked at proc tabulate, but the output data sets are also wide, though the display is tall. Thanks for the recommendation though.
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;
This is a cool procedure. I got a warning that it is expiring though. Thanks for the recommendation.
@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. 💣
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.