DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

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

 

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: 6690.416690.41
Other: 79.5973100.00

 

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

 

 Frequency Missing = 9
WHITE: 66100.0066100.00

Accepted Solutions
Solution
‎11-23-2016 07:30 PM
Super User
Posts: 5,085

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

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,651

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

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

PG
Contributor
Posts: 24

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

Thanks for replying!

 

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

Respected Advisor
Posts: 4,651

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

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

PG
Solution
‎11-23-2016 07:30 PM
Super User
Posts: 5,085

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

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

Contributor
Posts: 24

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

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: 66100.0066

100.00

 

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

 

Super User
Posts: 5,085

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

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"?
Contributor
Posts: 24

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

[ Edited ]

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 242 views
  • 1 like
  • 3 in conversation