If you want a table produced with values of 0, then I offer this solution. At first I thought it would be pretty straightforward, because PROC TABULATE has an option CLASSDATA= which allows you to specify the combinations of class variables that you want to summarize by. All combinations in CLASSDATA will appear in the output when not in the input data. However, I encountered an interesting quirk which I will explain below. First, let's create some test data from the SASHELP.CARS data set, because it seems to closely simulate your data. proc sort data=sashelp.cars out=have;
by origin;
run; I modified your tabulate to use the CARS data. Proc Tabulate data=have;
By origin; * values - Asia, Europe, USA;
Class type drivetrain;
Table type all, drivetrain all / printmiss misstext='0';
Run; It produces 3 tables by each origin. Here is what Asia looks like which should be similar to the crosstab of frequencies that you are trying to create. To use the CLASSDATA= option, you must create a data set with the variables and values that you want to see in a summary report. For this example, I want all combinations of the variables origin, type and drivetrain. * produce all combinations of class values;
proc sql;
create table classdata as
select
origin,
type,
drivetrain
from
(select distinct origin from sashelp.cars) a
cross join
(select distinct type from sashelp.cars) b
cross join
(select distinct drivetrain from sashelp.cars) c
;
quit; To prepare the test data for the next iteration, we remove the data where origin='Asia' to simulate a missing state in your data. This is where I thought that all we need to do is to modify the PROC TABULATE statement to add the CLASSDATA= and MISSING options. Also, the use of CLASSDATA requires that all variables in the CLASSDATA data set must be on the CLASS statement, so remove the BY and add origin to the CLASS statement as well as the MISSING option. By adding origin to the class statement first, it becomes the page dimension and produces separate tables for each origin as with the BY statement. However, after running the code, it did not produce a table for Asia as expected, but in the log a warning was written. * remove Asia from data;
data have2;
set have;
where origin in ('Europe', 'USA');
run;
*** does NOT work ***;
* when ALL values of class variables are null, no table/page is produced;
* Log - WARNING: A logical page containing only missing values has been deleted from the output.;
Proc Tabulate data=have2 classdata=classdata missing;
class origin type drivetrain / missing;
Table origin, type all, drivetrain all / printmiss misstext='0';
Run; The issue is when all of the values of the class variables are missing then PROC TABULATE will not produce a table. In this case, since the value Asia is not in the input data, then by extension all of the values for the subordinate class levels are also missing. It doesn't matter that CLASSDATA is specified and every option to include missing values is activated. The above code will work if only some of the lower class levels (type and drivetrain) had missing values in the data, as long as the page dimension is not missing any values. The tables will have 0's for the missing values for the row and column variables. All is not lost. The workaround is to produce the frequencies with PROC MEANS first. PROC MEANS also supports the CLASSDATA= option. However, it will always include all combinations of CLASSDATA in the output. Then you can still use PROC TABULATE to create the crosstabs with minor modifications. Here is the final solution. * calculate frequencies with proc means first;
* classdata option will produce 0 for combinations not in the input data;
proc means data=have2 classdata=classdata nway noprint;
class origin type drivetrain;
var cylinders;
output out=have_n n=freq; * produce frequencies;
run;
Proc Tabulate data=have_n classdata=classdata format=4.;
Class origin type drivetrain / missing;
var freq;
Table origin, type all, (drivetrain all)*freq / printmiss misstext='0' box=_page_;
Run; The procedure produces 3 tables by each origin including a table of 0's for the missing Asia.
... View more
![SASGF 2020 [Virtual] Attendee](/html/@A53A708409112314CE528E074D4C8C1F/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
