topic Re: Using Proc format to code multiple values to missing in SAS Procedures

Using Proc format to code multiple values to missing

HyunJee — Wed, 04 Jan 2012 16:04:07 GMT

I am recoding numeric values to character values. I am wanting the values of 77 and 99 to be coded to missing so they are not included in the frequency tables.

value C5R

. = " "

1 = "MOTHER (STEP, FOSTER, ADOPTIVE) OR FEMALE GUARDIAN"

2 = "FATHER (STEP, FOSTER, ADOPTIVE) OR MALE GUARDIAN"

3 = "GRANDPARENT"

4 = "OTHER FAMILY MEMBER/FRIEND"

77 = " "

99 = " "

I thought the above would do what I was wanting, but when I ran a proc freq that missing appeared in the table but the value was simply blank. I am not sure if this is possible to do.

any help is appreciated.

RELATIONSHIP OF RESPONDENT TO TEEN (RECODE)

Cumulative Cumulative

C5R Frequency Percent Frequency Percent

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

MOTHER (STEP, FOSTER, ADOPTIVE) OR FEMALE GUARDIAN 15859477 77.13 15859477 77.13

FATHER (STEP, FOSTER, ADOPTIVE) OR MALE GUARDIAN 3500044 17.02 19359521 94.15

GRANDPARENT 821907.6 4.00 20181429 98.15

OTHER FAMILY MEMBER/FRIEND 376138.2 1.83 20557567 99.98

4608.209 0.02 20562175 100.00 ******I want this value to appear as missing below and not be included in the frequency count or percent calculations.

Using Proc format to code multiple values to missing

Linlin — Wed, 04 Jan 2012 22:30:50 GMT

is this helpful?

proc format;

value C5R

. = " "

1 = "MOTHER (STEP, FOSTER, ADOPTIVE) OR FEMALE GUARDIAN"

2 = "FATHER (STEP, FOSTER, ADOPTIVE) OR MALE GUARDIAN"

3 = "GRANDPARENT"

4 = "OTHER FAMILY MEMBER/FRIEND"

77 = " "

99 = " ";

run;

data have;

input v;

format v c5r.;

cards;

;

proc freq data=have;

tables v;

run;

Using Proc format to code multiple values to missing

ArtC — Wed, 04 Jan 2012 23:00:58 GMT

You are almost certainly applying the format in the FREQ step. In FREQ the missing values are eliminated first, then the format is applied. Using the format in an earlier step (Linlin uses it in the DATA step) causes this timing to change.

Re: Using Proc format to code multiple values to missing

MikeZdeb — Thu, 05 Jan 2012 18:08:22 GMT

hi ... the only way you get those missing values in your table is by adding the MISSING option in PROC FREQ (no matter where you use the FORMAT statement ... data step or PROC) ... using Linlin's data ...

proc freq data=have;

table v / missing;

format v c5r.;

run;

did you use the MISSING option .. .if so, that's how the missing values end up in you table

if you did, what I don't understand is how the missing values ended up listed LAST in your table ... if MISSING data are present in a table in PROC FREQ they are always listed first (for either numeric or character data) no matter what you do (use a format, use an ORDER= option, use a WEIGHT option which you must have to get the non-integer frequencies in your table, whatever)

so, the table in your posting is a "mystery"

also, a suggestion ... you can group the values that are assigned to missing with the format ...

proc format;

value C5R

. , 77, 99 = " "

1 = "MOTHER (STEP, FOSTER, ADOPTIVE) OR FEMALE GUARDIAN"

2 = "FATHER (STEP, FOSTER, ADOPTIVE) OR MALE GUARDIAN"

3 = "GRANDPARENT"

4 = "OTHER FAMILY MEMBER/FRIEND"

;

run;

Re: Using Proc format to code multiple values to missing

Reeza — Thu, 05 Jan 2012 18:12:22 GMT

Here's a simple example to get what the OP has. It doesn't remove the missing either...not sure why.

On a Windows XP, SAS 9.2.3

Regardless of where the format is applied the results are the same.

data class;

set sashelp.class;

run;

proc format;

value age

11='Pre Teen'

12='Pre Teen'

13='Teen'

14='Teen'

15='Teen'

16=" ";

run;

proc freq data=class;

table age;

format age age.;

run;

Cumulative Cumulative

Age Frequency Percent Frequency Percent

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

Pre Teen 7 36.84 7 36.84

Teen 11 57.89 18 94.74

1 5.26 19 100.00

Re: Using Proc format to code multiple values to missing

Linlin — Thu, 05 Jan 2012 19:19:55 GMT

Hi Reeza,

In order to get the missing counts, there must be at least one missing value in the dataset.

data class;

set sashelp.class;

if name='Alice' then age=.;

run;

proc format;

value age

11='Pre Teen'

12='Pre Teen'

13='Teen'

14='Teen'

15='Teen'

.,16=' ';

run;

proc freq data=class;

table age;

format age age.;

run;

Cumulative Cumulative

Age Frequency Percent Frequency Percent

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

Pre Teen 7 41.18 7 41.18

Teen 10 58.82 17 100.00

Frequency Missing = 2

Linlin

Using Proc format to code multiple values to missing

MikeZdeb — Thu, 05 Jan 2012 19:25:10 GMT

Hi ... thanks, that explains the OP table ... must not have any real missing values.

As for your "not sure why" ... it doesn't get rid of missing since there are no missing values. The table is based on the original values of the variables, not formatted values. The OP must not have had any real missing values ... only the 77 and 99 that had a blank format label. Since 77 and 99 are greater than 1,2,3,4 ... they ended up last.

However, if there are no actual missing values, you cannot create missing data with a format and all observations end up in the table. That BLANK value in the table using SASHELP.CLASS is just a mask for 16.

If there actually were missing values in the OP data, they'd end up grouped with 77 and 99 and they'd all be considered as MISSING and would not be in the table

Using Proc format to code multiple values to missing

Reeza — Thu, 05 Jan 2012 19:43:36 GMT

That's fine, there's ways around it, just not the behaviour I would have expected by default. I do understand that SAS uses the underlying value rather than the actual formatted value.

If it was a space in a character value then it would be considered a missing observation.

If it is a space in the format of a numeric variable it IS considered a missing observation if there is other missing numeric observations, but if there are no missing observations it doesn't work.

I'd expect it to be treated the same whether or not there were other missing values. So in LinLin's example the table should have 1 missing and 1 blank not 2 missing.

Using Proc format to code multiple values to missing

art297 — Thu, 05 Jan 2012 23:59:32 GMT

Fareeza,

I've never confronted this before, but totally agree that it is not what one would or should expect. The value either is or isn't missing. I, personally, would consider this a bug that ought to be corrected. I'd be interested in hearing how anyone might consider this a feature.

Re: Using Proc format to code multiple values to missing

MikeZdeb — Fri, 06 Jan 2012 02:59:33 GMT

hi ... when a missing value is grouped with non-mssing values via a format, they all end up as missing in PROC FREQ

proc format;

value x

1-3 = '<4'

other = '4+'

;

run;

data x;

input x @@;

datalines;

1 2 3 . 5 6 7 8

;

run;

proc freq data=x;

table x;

format x x.;

run;

Cumulative Cumulative

x Frequency Percent Frequency Percent

<4 3 100.00 3 100.00

Frequency Missing = 5

the notes I use for my intro SAS class ... http://www.albany.edu/~msz03/epi514/notes/fp125_140.pdf ... discuss this on pages 138 and 139

I've always called it the "bad apple rule" ... one missing value spoils all the other non-missing in the group

ps Art Carpenter has a nice write up on this ... http://www.sascommunity.org/wiki/Missing_Values_in_Classification_Variables

with a link ... http://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_freq_sect015.htm

that states "When you use PROC FORMAT to create a user-written format that combines missing and nonmissing values into one category, PROC FREQ treats the entire category of formatted values as missing."

Re: Using Proc format to code multiple values to missing

art297 — Fri, 06 Jan 2012 04:04:25 GMT

Mike,

Again, while I wasn't familiar with the effect or the presentations (and they were nice indeed), I still agree with Fareeza that the effect SHOULD NOT be there!

Re: Using Proc format to code multiple values to missing

Tom — Fri, 06 Jan 2012 13:21:59 GMT

You will probably get better results by recoding the actual data values. You can use special missing values to distinguish amoung 77,99 and actual missing values.

Then those values will always be considered missing by PROC FREQ (and other analysis procedures).

Here is one way to convert using an INFORMAT and two different FORMATs. One that displays a decode for all of the values and other that maps the special values to blank.

proc format ;
value C5R  /* Normal reporting value decodes */
 1 = "MOTHER (STEP, FOSTER, ADOPTIVE) OR FEMALE GUARDIAN"
 2 = "FATHER (STEP, FOSTER, ADOPTIVE) OR MALE GUARDIAN"
 3 = "GRANDPARENT"
 4 = "OTHER FAMILY MEMBER/FRIEND"
 77,.A = "Not Applicable"
 99,.B = "Refused to answer"
 .  = "Not specified"
;
value C5M /* Collapse all missing values */
 .,.A,.B,77,99 = ' '
 other = [C5R50.]
;
invalue c5x /* Recoding Informat */
  77=.A
  99=.B 
;
run;
data want ;
  do c5=1 to 4,77,99,.;
    c5x = input(put(c5,2.),c5x.);
    output;
  end;
run;

Try using PROC FREQ will all combinations of the original variable (C5) and the recoded variable (C5X), missing option, and the C5R and C5M formats.

Re: Using Proc format to code multiple values to missing

Reeza — Fri, 06 Jan 2012 17:10:43 GMT

That is what the documentation says, but it does that ONLY if there are missing values.

If you have a format that assumes this, but have no actual missing values then the results end up in your table, not in the missing which isn't what I'd expect according to the documentation.

Using Proc format to code multiple values to missing

HyunJee — Tue, 17 Jan 2012 16:29:39 GMT

I apologize for such a delayed response. I went out of office for a time and am now back.

As I was reading through the response to my original post, I believe that according to what was written, if the variable I am trying to recode has no original missing or blank data, I cannot recode other values such as 77 or 99 to missing using a proc format?

thank you all for your responses, but if I am understanding what has been posted, there is not simple solution of the variable I am recoding has not orignial missing values.

If I am misinterpreting, please let me know. Thank you again.

HyunJee

Re: Using Proc format to code multiple values to missing

Linlin — Tue, 17 Jan 2012 16:51:26 GMT

Hi HyunJee,

What you interpreted is correct. In order to count 77, 99 as missing you have to have real missing values (.) in your dataset.

or you can pre-process you dataset:

data have ;

set have;

if v1 in (77,99) then v1 =.;

run;

Re: Using Proc format to code multiple values to missing

HyunJee — Tue, 17 Jan 2012 20:08:28 GMT

Thanks again for all your responses.

It appears there is not easy way to "fix" my problem. I was really hoping I could use proc format to recode all the variables, but it appears that I cannot do that if there are not actual missing data to begin with; which, unfortunately, several of my variables do not have missing values.

The only solution for me at this point is to recode every variable with the values I want as missing to missing.

Is there a way that SAS can fix this issue? Seems like it defeats the purpose of using a proc format to recode variables if in the end you have to end of having to recode each and every single one if they do not have any missing. Then you have to figure out which variables have no missing in the original data or just assume that all may possibly not have missing and recode each one, either way each option takes up time, which proc format is supposed to save you, but apparenlty not in the case with my current data.

Re: Using Proc format to code multiple values to missing

Tom — Tue, 17 Jan 2012 20:19:02 GMT

There are probably cases where users would like values formatted to blank to NOT be treated as a missing category by PROC FREQ, so I am not sure there is change that SAS could make that would satisfy all potential users.

Using values like 77 or 99 to represent a missing value is more the way that SPSS works. In SAS you would use special missing values like .A or .M.

So in your particular case I think you will benefit from recoding the actual stored values to special missing values instead. Either by making a copy of the dataset with the values recoded. Or a copy with new variables that have the special missing values.