BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eh51
Calcite | Level 5

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:

ObsmycatP_0P_1P_10P_25P_50P_75P_90P_99P_100
1No-3.47517-3.206838.9618486.291110.444116.45126.381133.746137.535
2Yes-4.86-3.3080.190835.658215.09662.953110.816128.516129.165

data want:

mycatNoYes
P_0-3.47517-4.86
P_1-3.20683-3.308
P_108.961840.19083
P_2586.2915.6582
P_50110.44415.096
P_75116.4562.953
P_90126.381110.816
P_99133.746128.516
P_100137.535129.165

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

7 REPLIES 7
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Reeza
Super User
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
Super User

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
Calcite | Level 5

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__
Jade | Level 19

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;

Capture.PNG

eh51
Calcite | Level 5

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

data_null__
Jade | Level 19

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 7 replies
  • 1201 views
  • 3 likes
  • 5 in conversation