topic Re: How to get proc tabulate to output a missing value? in SAS Programming

How to get proc tabulate to output a missing value?

EC27556 — Wed, 11 May 2022 12:42:51 GMT

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 = .?

Re: How to get proc tabulate to output a missing value?

Cynthia_sas — Wed, 11 May 2022 13:01:03 GMT

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

Re: How to get proc tabulate to output a missing value?

Quentin — Wed, 11 May 2022 13:04:55 GMT

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.

Re: How to get proc tabulate to output a missing value?

mkeintz — Wed, 11 May 2022 13:46:07 GMT

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

Re: How to get proc tabulate to output a missing value?

EC27556 — Wed, 11 May 2022 14:13:02 GMT

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!

Re: How to get proc tabulate to output a missing value?

EC27556 — Wed, 11 May 2022 14:16:05 GMT

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

Re: How to get proc tabulate to output a missing value?

Reeza — Wed, 11 May 2022 14:55:13 GMT

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

Re: How to get proc tabulate to output a missing value?

Tom — Wed, 11 May 2022 15:11:33 GMT

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