Creating output with proc tabulate where there are no observations

[G-Scott]

Hi All,

I have been searching around and am not sure it is even possible, but I was wondering if anyone knows a way to force sas to do this.

I am running a simple proc tabulate with a by statement. There are a few more of my by statement variables that do now have any options.

For example:

Proc Tabulate data=have;
By state;
Class cat1 cat2 / preloadfmt;
Table cat1 all, cat2 all / printmiss misstext=‘0’;
Format cat1 c1_var. Cat2 c2_var2.;
Run;

For my by statement, out of states 1 - 50,
States 22 and 37 do not have any observations this quarter, so they don’t come up in the output…but is there a way to force them to, showing all zeroes or null?

I have poked around online and found one article on the matter….but I am having a bit of a challenging time making it work and am hoping for a more…straightforward solution if possible.

I appreciate any insight!

[Ksharp]

/*
For such kind of scenoria,
I think you need a macro to conquer it.
*/
data have;
 set sashelp.heart;
run;



proc sql;
create table levels as
select distinct sex from have
union
select distinct 'X' from have
union
select distinct 'Y' from have
union
select distinct 'Z' from have
;
quit;


%macro tabulate(sex=);
data report ;
 set have;
if sex="&sex.";
run;

%let dsid=%sysfunc(open(report));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));


title "Sex is &sex.";
%if &nobs. ne 0 %then %do;
Proc Tabulate data=report ;
Class  status bp_status / preloadfmt ;
Table status all, bp_status all / printmiss misstext='0' ;
Run;
%end;
%else %do;
proc sql;select 'No obs found' as x label='#' from have(obs=1);quit;
%end;
%mend;


data _null_;
 set levels;
 call execute(catt('%nrstr(%tabulate(sex=',sex,'))'));
 run;

[ballardw]

---

@G-Scott wrote:
Hi All,

I have been searching around and am not sure it is even possible, but I was wondering if anyone knows a way to force sas to do this.

I am running a simple proc tabulate with a by statement. There are a few more of my by statement variables that do now have any options.

For example:

Proc Tabulate data=have;
By state;
Class cat1 cat2 / preloadfmt;
Table cat1 all, cat2 all / printmiss misstext=‘0’;
Format cat1 c1_var. Cat2 c2_var2.;
Run;

For my by statement, out of states 1 - 50,
States 22 and 37 do not have any observations this quarter, so they don’t come up in the output…but is there a way to force them to, showing all zeroes or null?

I have poked around online and found one article on the matter….but I am having a bit of a challenging time making it work and am hoping for a more…straightforward solution if possible.

I appreciate any insight!

---

Define "making it work".

You don't show what the desired result might be for when the By variable is missing or have no observations.

IF you have the BY variable value in the data set and play with some options you can get a table but it may not look the way that you want because you need to have something that will display missing values for the other.

If you actually have your BY variable with missing values for the other variables then adding MISSING to the CLASS statement should work but you may want to add a value for missing values to the Preloadfmt formats.

If you don't have the actual value of the BY variable in the data step then create a data set with just those values and MERGE that set with your report data before proc tabulate so the values are there and use the missing option.

I suspect that if the Tabulate is only as complex as you show I could summarize the data with proc summary and use a different report tool than tabulate as well but would really have to see the desired layout and have example data.

[FloydNevseta]

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.