BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pkfamily
Obsidian | Level 7

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
PGStats
Opal | Level 21

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

PG
pkfamily
Obsidian | Level 7

Thanks for replying!

 

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

PGStats
Opal | Level 21

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

PG
Astounding
PROC Star

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

pkfamily
Obsidian | Level 7

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?

 

Astounding
PROC Star

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"?
pkfamily
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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