topic Re: A simple transpose with multiple variables with a prefix, or different output to proc univariate in New SAS User

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

eh51 — Wed, 16 Feb 2022 21:44:06 GMT

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

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

yabwon — Wed, 16 Feb 2022 21:56:47 GMT

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

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

Reeza — Wed, 16 Feb 2022 21:59:05 GMT

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.

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

ballardw — Wed, 16 Feb 2022 22:34:07 GMT

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.

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

data_null__ — Thu, 17 Feb 2022 15:43:20 GMT

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;

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

eh51 — Thu, 17 Feb 2022 20:42:59 GMT

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

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

eh51 — Thu, 17 Feb 2022 20:43:50 GMT

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

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

data_null__ — Thu, 17 Feb 2022 22:01:28 GMT

@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. 💣

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