topic Re: WHERE versus IF IN subsetting unexpectedly different in SAS Certification

WHERE versus IF IN subsetting unexpectedly different

TheresaM — Fri, 28 Jul 2023 16:56:00 GMT

I understand that WHERE and IF IN can produce different results depending on where each statement is used. However, one of the questions on the (official) SAS 9.4 Certification Practice Exam has me puzzled. The question read:

This project will use data set cert.input36. At any time, you may save your program as program36 in cert\programs.

Write a SAS program that will clean the data in cert.input36 as follows:

Step 1:
create a temporary data set, cleandata36.
In this data set, convert all group values to upper case.
Then keep only observations with group equal to 'A' or 'B'.
Step 2:
Determine the MEDIAN value for the Kilograms variable for each group (A,B) in the cleandata36 data set. Round MEDIAN to the nearest whole number.
Step 3:
create results.output36 from cleandata36
Ensure that all values for variable Kilograms are between 40 and 200, inclusively.
If the value is missing or out of range, replace the value with the MEDIAN Kilograms value for the respective group (A,B) calculated in step 2.

Run the program and use the results to answer the next 3 questions.

Their suggested code:

data work.cleandata36;
   set cert.input36;
   group=upcase(group);
   if group in ('A','B');
run;

proc means data=work.cleandata36 median;
  class group;
  var kilograms;
run;

data results.output36;
  set cleandata36;
  if Kilograms < 40 or Kilograms > 200 then do;
    if group='A' then kilograms=79;
    else kilograms=89;
  end;
run;

proc contents data=results.output36;
run;

My code:

data work.cleandata36;
	set cert.input36;
	group = upcase(group);
	where group = 'A' or group = 'B';
run;

proc means data=work.cleandata36 mean min max median maxdec=1;
	class group;
run;

data results.output36;
	set cleandata36;
	if kilograms < 40 or kilograms > 200 then do;
		if group = 'A' then kilograms = 79;
		else kilograms = 89;
	end;
	if kilograms =. then do;
		if group = 'A' then kilograms = 79;
		else kilograms = 89;
	end;
run;
	
proc print data = results.output36;
run;

proc means data=results.output36 mean min max maxdec=1;
	class group;
run;

I've attached input36.sas7bdate in case anyone wanted to test.

When asked how many observations are in results.output36, my code (with the WHERE statement) resulted in 4897 observations. This was wrong, as their code (using IF IN) resulted in 4992. I tested my code using IF IN instead of WHERE and got the same result they did, 4992. But I'm not sure why. It would seem that both statements would subset to the same number of observations.

Also, their code is missing the portion that accounts for the instruction to replace missing values with the median -- I'm surprised they wouldn't include it in their suggested answer.

Re: WHERE versus IF IN subsetting unexpectedly different

PaigeMiller — Fri, 28 Jul 2023 17:00:37 GMT

IF and WHERE in a data step work differently. This is explained in the documentation: https://documentation.sas.com/doc/en/pgmmvacdc/9.4/lestmtsref/p1cxl8ifdt8u0gn12wqbji8o5fq1.htm#p08fp02gyb04apn1fmq0a6cu3ynd

Re: WHERE versus IF IN subsetting unexpectedly different

Mark2010 — Fri, 28 Jul 2023 17:08:58 GMT

@TheresaM Here is a bit more of a hint: Notice this bullet from step 1:

In this data set, convert all group values to upper case.

Then learn the difference between how IF and WHERE process data using the resource from @PaigeMiller and I think you will see why your code produces a different result.

Re: WHERE versus IF IN subsetting unexpectedly different

TheresaM — Fri, 28 Jul 2023 17:09:04 GMT

Hi Paige,

I thought I understood all those differences as outlined in the page you reference. However, the way I read all that -- it would seem that my use of WHERE would produce the larger subset (since it selects observations before they're brought into PDV), yet somehow IF IN subsetted a larger number of observations.

Re: WHERE versus IF IN subsetting unexpectedly different

TheresaM — Fri, 28 Jul 2023 17:17:17 GMT

Thank you, @PaigeMiller and @Mark2010 . I'll take some more time to understand the documentation!

Re: WHERE versus IF IN subsetting unexpectedly different

Quentin — Fri, 28 Jul 2023 18:31:34 GMT

One way to explore this is to make a small test dataset, like:

data play ;
  input group $1. ;
  cards ;
A
B
C
D
.
a
b
c
d
;

Then you could use that as an input to your WHERE data step and your subsetting IF data step and see how the results differ.

Re: WHERE versus IF IN subsetting unexpectedly different

TheresaM — Fri, 28 Jul 2023 18:31:59 GMT

ah! It took me few times rereading the documentation, but I think light finally dawned:

WHERE subsetted the data before UPCASE happened ("The WHERE statement selects observations before they are brought into the program data vector. "), and so even though I changed all values of "group" to upper case, it happened after WHERE selected its observations. Thus, my WHERE subset is smaller because it didn't have access to all the instances of "group" where 'a' and 'b' had been converted to upper case.

Re: WHERE versus IF IN subsetting unexpectedly different

Mark2010 — Fri, 28 Jul 2023 18:33:44 GMT

Bingo. Congrats on working through it.

Re: WHERE versus IF IN subsetting unexpectedly different

Quentin — Fri, 28 Jul 2023 18:36:18 GMT

You got it. So you could use WHERE, like:

where upcase(group) = 'A' or upcase(group) = 'B';

or:

where upcase(group) IN ('A' , 'B');

And it would select the same records as your IF. You would still need to use UPCASE inside the data step to upcase the values.

Re: WHERE versus IF IN subsetting unexpectedly different

Quentin — Fri, 28 Jul 2023 19:20:28 GMT

@TheresaM I suggest you select your own answer as the solution. @Mark2010 gave you a good hint, but you came up with the solution and wrote it as an answer. You also did a good job of phrasing the question clearly, and showing sample code. So if anyone finds it in the future, it would be good for them to see your answer as the solution. You should be able to click on Mark's answer and see an option to "unmark as correct" or something like that.

Re: WHERE versus IF IN subsetting unexpectedly different

mkeintz — Mon, 07 Aug 2023 15:10:51 GMT

@TheresaM

I know this is a (very) lagged response. But ...

I find it most useful to regard the WHERE statement (or where option as a dataset name parameter) as a process outsourced to the data engine.

That's why WHERE can be used in PROC's as well as DATA steps - unlike IF.

Re: WHERE versus IF IN subsetting unexpectedly different

TheresaM — Tue, 01 Aug 2023 13:32:24 GMT

Hi @Quentin

Thank you for this. This actually made me really happy to read! Everything I thought I understood became much more of a struggle as I actually try to put it in practice (or answer practice exam questions). So, hearing that I actually "came up with a solution" gives me hope. 😊🎉

Re: WHERE versus IF IN subsetting unexpectedly different

TheresaM — Mon, 07 Aug 2023 14:41:08 GMT

Hi @mkeintz ,

Thanks for this too! Where vs IF, when to use the = sign vs when not to, DO while vs DO until... so much to remember!! I know it will become easier to remember the more programs that I write, but until then, every tip helps. So thanks for responding to my post 🙂 🙂

Re: WHERE versus IF IN subsetting unexpectedly different

DBsky — Tue, 01 Jul 2025 10:11:57 GMT

Use IF when you need to filter based on new or transformed variables.
Use WHERE when filtering on existing dataset variables, and in PROC steps, for better efficiency.

note:
With IF, this upcase() runs before filtering, so lowercase “a” and “b” are converted, then kept.
With WHERE, the condition is evaluated before upcase(). Since it checks the original values, lowercase “a” or “b” will not match and get dropped.

That's why you get different obs number with where and if.

incase of missing data-

data results.output36;
set cleandata36;
if Kilograms = '.' or 40>Kilograms or Kilograms>200 then do;
if group='A' then Kilograms=79;
else Kilograms=89;
end;
run;