Numeric values in PROC FREQ or formatted values in PROC PRINT

Question

I have a situation that seems like it should be easy to fix. But, I’m struggling to find an elegant solution. I was given data that was already formatted. Similar to the toy dataset below.

proc format;
    value   x1_f    1 = "Yes"
                    0 = "No";
    value   x2_f    1 = "Yes"
                    2 = "No";
run;

data ds;
    input x1 x2;
    datalines;
    1 2
    1 1
    0 1
    ;

data ds;
    set ds;
    format  x1 x1_f.
            x2 x2_f.;
run;

Now, as part of my data management process I create a 2x2 table using x1 and x2. Let’s say I’m checking my data, and expect x1 and x2 to always agree.

proc freq data = ds;
    tables x1*x2;
run;

When I look at the report I notice that x1 and x2 don’t always agree. So, I want to print the observations that don’t agree to see if I can figure out what might be going on. Because this is a toy example, there are not other variables to look at, but hopefully you get the idea.

proc print data = ds;
    where x1 = "Yes" & x2 = "No";
run;

SAS gives me the following error:

ERROR: WHERE clause operator requires compatible variables

Ok, I guess I need to give SAS the numeric values instead of the formatted values. But, when I go look at the PROC FREQ report from earlier, it only shows me the formatted values. So, I run another PROC FREQ.

proc freq data = ds;
    tables x1*x2;
    format x1 x2;
run;

Now I can see which variable uses 0’s and 1’s, and which variable uses 1’s and 2’s.

proc print data = ds;
    where x1 = 0 & x2 = 1;
run;

Finally, I get what I’m looking for. This just seems really clunky and inelegant. Can someone tell me how to either view my numeric values and formatted values simultaneously in my frequency report, OR how to use the formatted values in proc print?

Tom Tom · Accepted Answer · 2017-03-19T00:38:24

If you know the format name then use the PUT() function in the WHERE statement.

proc print data=sashelp.class ;
  where put(age,2.) = '12';
run;

If you don't know the format name then you can use the VVALUE() function. But you probably need to add a data step for it to work.

data to_print;
  set sashelp.class ;
  if strip(vvalue(age))='12';
run;
proc print data=to_print;
run;

In the old days I used to just create a separate format catalog with formats that included the values in the labels.

proc format;
  value x1_f 1 = "1=Yes" 0 = "0=No";
run;

Then when you read your output you knew the values the variables actually had. It is pretty simple to create a program to convert a format catalog. http://github.com/sasutils/macros/blob/master/cfmtgen.sas

Numeric values in PROC FREQ or formatted values in PROC PRINT

1 Answers