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:
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:
WHITE: 66 | 100.00 | 66 | 100.00 |
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 > ' ';
What are the values of race that should be counted in Other and not Missing?
Thanks for replying!
'30', '42', '45', '48', '53'
What are the values of race that should be counted in Other and not Missing?
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 > ' ';
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 | 66 | 100.00 |
Under your explanation, why would 'Other' still be assigned a value of missing?
That definitely shouldn't happen! Things I would look at:
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.