topic PROC TABULATE- Suppressing Columns in SAS Procedures

PROC TABULATE- Suppressing Columns

sam_sas2 — Thu, 27 May 2021 17:45:54 GMT

I am currently running this following code,

proc tabulate data=sashelp.cars;
class origin drivetrain type;
var msrp;
table (drivetrain * (N))
	  (msrp * (N MEAN MEDIAN MIN MAX)),
	  ALL origin type/ misstext='0';
run;

The current output looks something like this,

		All	Origin			Type
			Asia	Europe	USA	Hybrid	SUV	Sedan	Sports	Truck	Wagon
DriveTrain
All	N	92	34	36	22	0	38	28	5	12	9
Front	N	226	99	37	90	3	22	179	8	0	14
Rear	N	110	25	50	35	0	0	55	36	12	7
MSRP	N	428	158	123	147	3	60	262	49	24	30
	Mean	32774.86	24741.32	48349.8	28377.44	19920	34790.25	29773.62	53387.06	24941.38	28840.53
	Median	27635	23032.5	40590	25520	20140	32062.5	26432.5	40590	22180	25545
	Min	10280	10280	16999	10995	19110	17163	10280	18345	12800	11905
	Max	192465	89765	192465	81795	20510	76870	128420	192465	52975	60670

The issue I'm facing currently is, I do not want to display columns Sedan and Sports but at the same time I do not want my over all count for Origin or All to get affected. Is it possible to achieve this by any means?

Thanks in advance! 🙂

Re: PROC TABULATE- Suppressing Columns

sbxkoenk — Thu, 27 May 2021 18:06:34 GMT

Hello,

It has been many, many years since I have last used PROC TABULATE, so I do not know very well about the best answer.

You can have two 'table' statements of course in the same PROC TABULATE, one with ORIGIN and one with TYPE.

Or you can save the output table in an output dataset with ODS OUTPUT and work further from there.

As in:

ods trace on;
ods output table=work.table;
proc tabulate data=sashelp.cars;
...
run;
/* end of program */

You can alternatively do some base SAS programming to get what you want or maybe it is easier to do with PROC REPORT? I think in PROC REPORT it is easier to suppress columns in the display while they are still used for some of the statistics.

Koen

Re: PROC TABULATE- Suppressing Columns

Cynthia_sas — Thu, 27 May 2021 19:13:02 GMT

Hi:
The usual way that this is accomplished is to create a summary dataset with one pass through the data to get all the numbers and statistics you need for each of the grouping variables. Then, you'd need to eliminate the group values (such as Sedan and Sports) from the data which would not impact the statistics already calculated. Then, you'd use this pre-processed summary data to produce your final report. You could generate the summaries with PROC TABULATE using OUT= or with PROC MEANS or even PROC SQL. However, the issue I see with this is that if you do NOT explain that your final report has eliminated some of the groups, people will not be able to reconcile the totals for Origin (in your example) with the totals for type and the MEAN, MEDIAN, MIN and MAX could be impacted as well. I tend to avoid this type of exercise because it leads to confusion. My usual way to deal with it would be to re-classify SPORTS and SEDAN as an "OTHER" category with a user-defined format and then it would still appear on the report but with less emphasis than the other TYPE values. But that way, all the counts would match if someone went back to the original input data.

Cynthia