How to get proc tabulate to output a missing value?

[EC27556]

Hi I am having some trouble getting my proc tab to output a missing value.

Here is the proc tab (for reference, the variable "one" is a variable that=1 for all observations):

proc tabulate data = Have out = Want;
class GroupA GroupB GroupC GroupD ap_month;
var count one;
table GroupA=''*GroupB=''*GroupC=''*GroupD=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge;
run;

Which gives a table that looks like this:

and output dataset that looks like this:

However, in the table you can see a missing value. This missing value does not appear in the output dataset. You can see in between the highlighted rows, there is no 01/03/2021. Is there any way to make proc tabulate output a row in the output dataset for 01/03/2021, where one_sum would = .?

[Cynthia_sas]

Hi:
I think you need to investigate the PRINTMISS option of the TABLE statement. It's good to see your code but without some test data, no one can run your code.
Cynthia

[mkeintz]

@Cynthia_sas 

I think your suggestion of printmiss does it.   The want_printmiss dataset below has an observation corresponding to the missing cell in the table and absent from the want_normal dataset.

data have;
  input x y  ;
datalines;
1 1
1 2
2 1
run;
proc tabulate data=have out=want_normal;
  class x y  ;
  tables x,y  ;
run;
proc tabulate data=have out=want_printmiss;
  class x y  ;
  tables x,y  /printmiss;
run;

[EC27556]

Hi, here is some example code:

data person;
   input GroupA GroupB GroupC ap_month $ one;
   datalines;
1 1 1 Jan2010 1
1 1 2 Jan2010 1
1 2 1 Jan2010 1
2 1 1 Jan2010 1
2 2 1 Jan2010 1

1 1 1 Jan2011 1
1 1 2 Jan2011 1
1 2 1 Jan2011 1
2 1 1 Jan2011 1

;
/*2 2 1 Jan2011 1*/
run;

proc tabulate data = Person out = want;
class GroupA GroupB GroupC ap_month;
var one;
table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge ;
run;
proc tabulate data = Person out = want2;
class GroupA GroupB GroupC ap_month;
var one;
table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge printmiss;
run;

Here you can see an example of the situation I have going on. You can see the commented out line is basically the missing data - which is then missing in the first proc tabulate table. By using printmiss, it expands the table (which i dont want) and, whilst the wanted output observation is now included in the output data, all the other observations relating to this bigger table are now also included - which i dont want!

[Quentin]

Try adding the MISSING option to the PROC TABULATE statement.

proc tabulate data = Have out = Want MISSING;
class GroupA GroupB GroupC GroupD ap_month;
var count one;
table GroupA=''*GroupB=''*GroupC=''*GroupD=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge;
run;

If that doesn't work, try adding it the CLASS statement

proc tabulate data = Have out = Want MISSING;
class GroupA GroupB GroupC GroupD ap_month / MISSING;
var count one;
table GroupA=''*GroupB=''*GroupC=''*GroupD=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge;
run;

See also other TABULATE options for dealings with missing, such as MISSPRINT and MISSTEXT.

[EC27556]

@mkeintz 

The missing option does not seem to help unfortunately.

Here is some example code:

data person;
   input GroupA GroupB GroupC ap_month $ one;
   datalines;
1 1 1 Jan2010 1
1 1 2 Jan2010 1
1 2 1 Jan2010 1
2 1 1 Jan2010 1
2 2 1 Jan2010 1

1 1 1 Jan2011 1
1 1 2 Jan2011 1
1 2 1 Jan2011 1
2 1 1 Jan2011 1

;
/*2 2 1 Jan2011 1*/
run;

proc tabulate data = Person out = want;
class GroupA GroupB GroupC ap_month;
var one;
table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge ;
run;
proc tabulate data = Person out = want2;
class GroupA GroupB GroupC ap_month;
var one;
table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month  /nocellmerge printmiss;
run;

Here you can see an example of the situation I have going on. You can see the commented out line is basically the missing data - which is then missing in the first proc tabulate table. By using printmiss, it expands the table (which i dont want) and, whilst the wanted output observation is now included in the output data, all the other observations relating to this bigger table are now also included - which i dont want!

[Reeza]

That's a missing category, not missing value. How does SAS know to add in something that doesn't exist in a data set? 

You can use a PRELOADFMT or CLASSDATA to add in categories that are missing/non existent in your data.

https://www.lexjansen.com/nesug/nesug11/cc/cc29.pdf

[Tom]

Why are you using TABULATE instead of SUMMARY if the goal is a DATASET?