Using where statement in proc freq

[ChuksManuel]

Hi programmers,

I am trying to test for association of 2 variables using the proc freq procedure for a predictor that has 4 levels against an outcome that has 5 categories.

I specifically want to select 2 levels of the predictor and not use all 4 in this test. I have tried the "where" statement but i haven't gotten any success.

I like to know where the issue could come from.

proc freq data = A;
tables group*birth
/chisq cmh;
where birth = ('Yes'  'No');
run;

[PaigeMiller]

where birth in ("Yes" "No");

[ChuksManuel]

Hi I did that and it's telling me that there were zero observations read from the dataset.

[Kurt_Bremser]

Always beware of the "hidden blanks" used to patch up the unused space in character variables:

where strip(birth) in ("Yes","No");

And make sure you get your spelling absolutely right. "Yes" ^= "yes"!

[PaigeMiller · Accepted solution]

---

@ChuksManuel wrote:
Hi I did that and it's telling me that there were zero observations read from the dataset.

---

In addition to what @Kurt_Bremser said, you need to look at the data set with your own eyes to see why "Yes" is not found in the data set, and why "No" is not found in the data set. It is possible that your variable BIRTH is formatted and so the actual values are 0 or 1 (or some other values).

You can look at the unformatted values by running PROC FREQ again without the WHERE statement and adding in

format birth;

which removes the formatting from the variable, or in whatever data set viewer you use you can also indicate that you want to remove the format from BIRTH.

But here we get into the problem that you are getting incorrect results, and you have not shown us the LOG, which would be extremely helpful, and should be something you show us every single time there are problems identified in the LOG. That's EVERY SINGLE TIME.

[ChuksManuel]

Yes. Formatting was the reason.

[Tom]

You need IN operator not equality operator to select multiple values.

The values have be the actual values of the variable, not the formatted values.  

For character variables the value has to match exactly.  Case matters. Trailing spaces don't matter, but leading spaces do.

where lowcase(left(birth)) in ('yes'  'no');

If the variable is actually numeric with a format that displays two (or more) of the values as Yes and No then you need to use the actual numeric values in your list of values. 

where birth in (0 1);

Or perhaps use the format in the WHERE statement.

where put(birth,birthfmt.) in ('Yes' 'No');