The SAS Output Delivery System and reporting techniques

Total column in Proc Tabulate

Reply
Occasional Contributor
Posts: 18

Total column in Proc Tabulate

[ Edited ]

Hi all, 

 

I need some help with Proc Tabulate!  I hope you can help me because I'm pulling my hair out.

 

I need to generate the table in the exact format as below:

SAS proc tab question1.JPG

 

I can only figure out some of it.  Below is my code:

proc tabulate data=data.ads  FORMAT=9.0 order=formatted;
var FEMALE LATINO_FLAG AGE_25_OLDER HSGPA BESTSAT;
CLASS DIRECT_ADMIT;
table DIRECT_ADMIT ='' *(sum Mean*f=PERCENT9.1), FEMALE LATINO_FLAG AGE_25_OLDER
/ ROW=FLOAT RTS=22 MISSTEXT='-' ;
KEYWORD all / style=[bordertopcolor=black bordertopwidth=1 bordertopstyle=double font_weight=bold fontstyle=italic];
run;

 

Since I can't share the data, I will use sashelp.shoes as an example:

proc tabulate data=sashelp.shoes;  
  where region in ('Asia', 'Canada') and product in ('Boot', 'Slipper');
  class product region;
  var sales returns stores inventory;
  table region * (sum mean),
        sales returns inventory stores;
run;

 

I'm able to achieve something like below (see screen cap), but I need to add:

- total column N

- total row N

- merged column heading 

- means columns at the end

SAS proc tab question2.JPG

Any help would be appreciated! Thank you!

Super User
Posts: 11,810

Re: Total column in Proc Tabulate

Proc tabulate really doesn't do crossed statistics in a single table: row with mean or sum and column of n for example. To stack vertical statistics as you show the sum and mean would have to remain in the row dimension as you show

 

If you need output that looks exactly like your output it may be possible with the Report Writing Interface which would require using other steps to summarize the data before display.

 

If you were willing to accept the statistics all in column (  sum beside % instead of sum above % tabulate might be possible but would likely require some restructuring of your data.

 

For tabulate to do a grouped header as you show you would want one variable as a class variable that has values (or formatted values) of Male Latino and "Older than 25" similar to use of Product here:

proc tabulate data=sashelp.shoes;  
  where region in ('Asia', 'Canada') and product in ('Boot', 'Slipper');
  class product region;
  var sales returns stores inventory;
  table region * (sum mean),
        product*(sales returns inventory stores);
run;

Means as a column won't cross with sum and mean as row.

 

Ask a Question
Discussion stats
  • 1 reply
  • 282 views
  • 2 likes
  • 2 in conversation