Format is applied to new contructed variable but not to existing variables

[Wolverine]

I created a yes/no/missing format. It works when I apply it to a variable that I constructed in my syntax, but not to variables that were already present in the data file. Could the existing variables already have a format that is being retained? And if so, how do I override it?

The syntax below will produce a freq table for Any_cancer with 1:Yes, 2: No, Inapplicable/Missing, in that order.  This is a constructed variable, and the output is being put into a table. So having it in the correct order is important.

The table for HLT_OCSTROKE shows 2, 1:Yes, Inapplicable/Missing, in that order. This is an existing variable in the data, and there are about 10 more that are doing the same thing.

I would add sample data, but I can't see how it would fail to run properly with new data. The only thing that makes sense is that the formatting is retained from the original data.

LIBNAME temp "C:\SAStemp";

proc format library=temp;

value yesfmt_r
1='1:Yes'
2='2:No'
.='Inapplicable/Missing'
.R='Inapplicable/Missing'
.D='Inapplicable/Missing'
.N='Inapplicable/Missing';
RUN;

DATA temp.want; SET temp.have;
FORMAT
Any_cancer	YESFMT_r.
HLT_OCSTROKE	YESFMT_r.;
RUN;

PROC FREQ data=temp.want; TABLES Any_cancer HLT_OCSTROKE / missing; RUN;

[PaigeMiller]

The syntax below will produce a freq table for Any_cancer with 1:Yes, 2: No, Inapplicable/Missing, in that order.  This is a constructed variable, and the output is being put into a table. So having it in the correct order is important.

The order in PROC FORMAT is irrelevant and not used. If you want the results in a particular order in the PROC FREQ output, then you need to use the ORDER= option in PROC FORMAT.

I think you will need to sort the data so that the categories are in the order you want them in after the sort, and then in PROC FREQ use ORDER=DATA.

Lastly, the usage of '1: Yes' instead of 'Yes' and '2: No' instead of 'No' seems un-professional to me, and unnecessary. Take the numbers out of the format, and sort the data in the right order and use ORDER=DATA.

[Tom]

This sentence 

The only thing that makes sense is that the formatting is retained from the original data.

makes no sense.

A FORMAT is just instructions for how to display the values.  Changing the format attached to the data cannot change the data.   That a different format was used to display the values before would not have any impact on displaying the values now.

Also this step is not needed.

DATA temp.want;
  SET temp.have;
  FORMAT Any_cancer YESFMT_r. HLT_OCSTROKE YESFMT_r.;
RUN;

Just add the FORMAT statement to the PROC step if want that step to use those formats when displaying those variables.

[Tom]

This step is writing the format into the catalog named TEMP.FORMATS.

proc format library=temp;
value yesfmt_r
 1='1:Yes' 
2='2:No'
.='Inapplicable/Missing'
.R='Inapplicable/Missing'
.D='Inapplicable/Missing'
.N='Inapplicable/Missing'
;
RUN;

Is that catalog included in the list of catalogs that SAS will search when trying to find the format?

And if so does TEMP.FORMATS come BEFORE any other catalogs that might have a FORMAT with the same name included in it?

Check the value of the FMTSEARCH system option.

%put %sysfunc(getoption(FMTSEARCH));

[Reeza]

The code shown applies it to only new variables, can you show how you're applying it to old variables?

And the log?

[Tom]

Here is an example showing how to use three different formats to display the exact same values in different ways with PROC FREQ.

data have;
  do x=.,.a,.n,.r,.d,1,2; 
     yn=x; ynr=x;
     output; 
  end;
run;

proc format ;
value yn 1='Yes' 2='No';
value ynr .,.n,.r,.d = 'Invalid Data' 1='Yes' 2='No';
run;

proc freq data=have;
  tables x yn ynr / missing;
  format yn yn. ynr ynr. ;
run;