topic Total column in Proc Tabulate in ODS and Base Reporting

Total column in Proc Tabulate

lin39 — Fri, 01 Sep 2017 16:29:27 GMT

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:

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

Any help would be appreciated! Thank you!

Re: Total column in Proc Tabulate

ballardw — Fri, 01 Sep 2017 17:07:54 GMT

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.