topic Re: Proc Freq showing up as Missing when Format is Specified as "Other" in SAS Programming

Proc Freq showing up as Missing when Format is Specified as "Other"

pkfamily — Wed, 23 Nov 2016 22:37:12 GMT

When I apply the "other" format in proc freq (SAS 9.4), the table shows it as missing rather than "other". Has anyone ever encountered this problem before?

proc format;
value $racef 
  '10'='White' 
   other = 'Other';
run;
 
proc freq data=pc_data;
format race $racef.;
table race;
run;

So if things work as expected, the code above should yield:

Frequency Missing = 2

WHITE: 66	90.41	66	90.41
Other: 7	9.59	73	100.00

But Instead, I'm getting the output below. I can post an actual dataset with the issue if needed:

Frequency Missing = 9

WHITE: 66

100.00

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

PGStats — Wed, 23 Nov 2016 22:54:59 GMT

What are the values of race that should be counted in Other and not Missing?

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

PGStats — Wed, 23 Nov 2016 22:55:00 GMT

What are the values of race that should be counted in Other and not Missing?

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

pkfamily — Wed, 23 Nov 2016 23:28:56 GMT

Thanks for replying!

'30', '42', '45', '48', '53'

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

Astounding — Thu, 24 Nov 2016 00:15:46 GMT

The short answer: You need to add the MISSING option:

tables race / missing;

The longer answer: Within each grouping that the format defines, SAS stores a single value not a set of values. It stores the lowest value that it finds that falls within the grouping. In this case, missing is the lowest value that actually appears in the data and that falls within the "Other" range. So SAS stores a missing value to represent the "Other" range and then decides that missing values should not be printed. As an experiment, you coudl run the PROC FREQ (without the MISSING option) and add a WHERE statement:

where race > ' ';

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

pkfamily — Thu, 24 Nov 2016 00:35:46 GMT

Thank you! And I do appreciate the long answer. However, I do have a tiny qualm. When setting the format as:

proc format;value $racef   '10'='White'  ''='Missing'  other = 'Other';run;

I still get:

Frequency Missing = 9

WHITE: 66

100.00

Under your explanation, why would 'Other' still be assigned a value of missing?

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

Astounding — Thu, 24 Nov 2016 00:41:10 GMT

That definitely shouldn't happen! Things I would look at:

Does it make a difference if you add a blank in the quotes: ' ' = 'Missing'
Did you accidentally define "Other" before defining "Missing"?

Re: Proc Freq showing up as Missing when Format is Specified as "Other"

pkfamily — Thu, 24 Nov 2016 00:45:05 GMT

Adding a space in ''='Missing' did the trick! This is bit disconcerting, since I have always been using '' and not ' ' when denoting missing values for character variables.... Thanks again though!