topic Re: Proc Tabulate Drops Permutations of Values in SAS Programming

Proc Tabulate Drops Permutations of Values

DavidPhillips2 — Wed, 12 Jan 2022 20:23:44 GMT

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;

Re: Proc Tabulate Drops Permutations of Values

data_null__ — Wed, 12 Jan 2022 20:47:23 GMT

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

Re: Proc Tabulate Drops Permutations of Values

DavidPhillips2 — Wed, 12 Jan 2022 20:52:31 GMT

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

Re: Proc Tabulate Drops Permutations of Values

ballardw — Wed, 12 Jan 2022 22:42:49 GMT

@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.