Reset special numeric missing values to original value to output/export

[mmmb]

I used the following code to format my data for cleaning:

proc format;

%let mis_inval='777'=.a '888'=.b;

%let mis_val=.a='Dont Know' .b='Refused';

%let mis_valstr='777'='Dont Know' '888'='Refused';

invalue demo2_ &mis_inval other = [best32.];

value demo2_ 1='Yes' 0='No'  &mis_val;

run;

data test;

set test;

informat demo2 demo2_.;

format demo2 demo2_.;

run;

I'd like to export the new dataset (with new variables I have created but not showing here) but with the original values (777 or 888), not the special numeric missing variables. It always exports as B or A.

Thank you!

[andreas_lds]

1. Replace the %let-statements as they are not necessary at all.
2. Don't overwrite "test" with the data step.
3. The informat-statement changes the informat attribute of a variable and has no effect, unless the variable is used in an input statement.
4. A format changes the appearance of a value, not the value itself. so maybe removing the assigned format solves the issue.

[Tom · Accepted solution]

What do you mean by the word "export"?  Did you convert the SAS dataset into something else? What? How?

If you PRINT the values the FORMAT will determine how the values are displayed.  So you can "export" your data to an XLSX sheet by using ODS EXCEL and PROC PRINT (or PROC REPORT).  If you want a format that will display .A as 777 and normal numbers as expected then you probably want something like this:

proc format ;
  value miss2num .a='777' .b='888' other=[best12.];
run;

Your data step does not make much sense to me either.  Why bother to attach the informat when you are not using an INPUT statement to assign new values to the variable?  Is it just to provide documentation of how you transformed the special valid numeric values like 777 into special missing values instead?

If you want to actually transform .A back into 777 then you cannot do it directly using formats or informats.  That is because FORMATS convert values into text and INFORMATS convert text into values.  Numeric formats and informats work with numeric values.  Character formats and informats work with character values. So neither formats nor informats can be used to convert numeric values directly into other numeric values.  Instead you would need to use a combination of both PUT() and and INPUT() function calls.

So this step will make a new variable called DEMO2_RAW that will have the original numeric values of 777 and 888 instead of the special missing values of .A and .B.  It uses the MISS2NUM. format to convert the .A into "777" and then uses the 32. informat to convert that string of digits back into a number.  You can use format/informat statement with variables but no format specification to remove any formats that might have been attached so the actual values will display.

data test_recoded;
  set test;
  demo2_raw = input(put(demo2,miss2num.),32.);
  informat demo2 demo2_raw ;
  format demo2 demo2_raw ;
run;