Proc Tabulate Drops Permutations of Values

[DavidPhillips2]

I'm running into an odd sort of bug.  When I run a shorter tabulate statement I have a report with four distinct values of award_category_desc when I include more variables the output returns two distinct values of award_category_desc.  Due to legal reasons, I cannot post the dataset.  The values of the field are:

Doctoral Degree
Masters Degree
Post Masters Certificate
Postbaccalaureate Certificate

/*This block correctly returns a tabular that has four distinct values of award_category_desc*/
proc tabulate data=Time_to_Degree;	
	var Award_Count Time_to_Degree;
	class academic_year award_category_desc;
	table (award_category_desc="") , 
	academic_year *(Award_Count*f=8.0 Time_to_Degree*mean*f=8.1) all;
run;	
/*This block correctly returns a tabular that has four distinct values of award_category_desc*/
proc tabulate data=Time_to_Degree;	
	var Award_Count Time_to_Degree;
	class academic_year award_category_desc program_desc;
	table award_category_desc=""*program_desc="" , 
	academic_year *(Award_Count*f=8.0 Time_to_Degree*mean*f=8.1) all;
run;	

/*This block incorrectly returns a tabular that has two distinct values of award_category_desc as if I have a where clause*/
proc tabulate data=Time_to_Degree;	
	var Award_Count Time_to_Degree;
	class academic_year award_category_desc program_desc first_concentration_desc;
	table award_category_desc=""*program_desc=""*first_concentration_desc="" , 
	academic_year *(Award_Count*f=8.0 Time_to_Degree*mean*f=8.1) all;
run;	

[data_null__ · Accepted solution]

Missing values for the added class variables is causing those observation to be ignored.  Use the MISSING option.

[DavidPhillips2]

Thanks that solved the issue.  It is odd that missing is required to not produce what seems like a bug.

[ballardw]

---

@DavidPhillips2 wrote:

Thanks that solved the issue.  It is odd that missing is required to not produce what seems like a bug.

---

Pretty well documented in the Proc Tabulate syntax for Class statement options MISSING.

Depending on the actual needs for your report you can add a variable that is occasionally missing to create filters in tables by nesting that variable in only some tables.

Another approach would be to add an appropriate variable to contain the "nested" class variables such as with one of the CAT functions. Then the only records not used would be where ALL of the nested variables were missing. I admit that might be odd to read but is an option where the values are pretty obvious.