topic How to output missing categories with proc tabulate in SAS Programming

How to output missing categories with proc tabulate

kashlik123 — Fri, 12 Nov 2021 09:50:12 GMT

I have a table with two group columns. One is a time variable and the other is just a group like the one below.

	Data have(drop=x);
	Do x = 1 to 6;
		year = 2010;
		group = 2;
		output;
	End;
	Do x = 1 to 6;
		year = 2020;
		group = ceil(x/3);
		output;
	End;
	Run;

I am attempting to calculate the share of observations by year and group in the following manner:

	Proc tabulate data=have out=not_want(drop=_type_ _page_ _table_);
	Class year / missing;
	Class group / missing;
	Table group='',year=''*colpctn='';
	Run;

The results window shows the desired results, however the output table does not contain the missing value from the year 2010. This is the expected output:

	Data want;
	year = 2010; group=1; share=100; output;
	year = 2010; group=2; share=.; output;
	year = 2020; group=1; share=50; output;
	year = 2020; group=2; share=50; output;
	Run;

Re: How to output missing categories with proc tabulate

PeterClemmensen — Fri, 12 Nov 2021 09:57:29 GMT

Is it a requirement to do this with Proc Tabulate?

Re: How to output missing categories with proc tabulate

kashlik123 — Fri, 12 Nov 2021 10:03:21 GMT

If there is another solution, which is just as compact and fast then sure, other option can be considered. For example doing this through PROC SQL takes too much time, as my real dataset is quite big.

Re: How to output missing categories with proc tabulate

PeterClemmensen — Fri, 12 Nov 2021 10:08:22 GMT

Ok. You can use the Printmiss Option in the Table Statement like this.

This will give you a 0 in the output pct, which is more correct than a missing value.

Let me know if this works for you.

Proc tabulate data=have out=not_want(drop=_type_ _page_ _table_);
   Class year / missing;
   Class group / missing;
   Table group='',year=''*colpctn='' / printmiss;
Run;