Hello,
I am preparing for the base certification and hoping I can get some help understanding this question I have gotten wrong a few times. The first step is a pretty basic ask but I got it wrong because I am using if rather than when.
Write a SAS program that will clean the data in cert.input36 as follows:
Below is what I wrote:
DATA CLEANDATA36;
SET CERT.INPUT36;
GROUP=UPCASE(GROUP);
WHERE GROUP IN ('A', 'B');
RUN;
PROC MEANS DATA=CLEANDATA36 MEDIAN;
VAR KILOGRAMS;
CLASS GROUP;
RUN;
I get it wrong but change the where to IF and get it correct can anyone help me understand this.
While the code works the question is how many observations are there after this step, the correct answer being 4992. With the where clause I get 4897.
WHERE filters the data set before it's read in. If you had a value of "a" in the original data set it wouldn't get filtered out.
Best way to figure this is out is to test it!
data group_data;
input group $;
cards;
a
A
a
b
a
a
C
B
c
d
a
G
B
A
A
;;;;
run;
title 'Example WHERE';
data example1;
set group_data;
group = upcase(group);
where group in ('A', 'B');
run;
proc print data=example1;run;
title 'Example IF';
data example2;
set group_data;
group = upcase(group);
IF group in ('A', 'B');
run;
proc print data=example2;run;
And notice the output is different!!!
WHERE:
Example WHERE Obs group 1 A 2 B 3 B 4 A 5 A
IF:
Obs group
1 A
2 A
3 A
4 B
5 A
6 A
7 B
8 A
9 B
10 A
11 A
@JC411911 wrote:
Hello,
I am preparing for the base certification and hoping I can get some help understanding this question I have gotten wrong a few times. The first step is a pretty basic ask but I got it wrong because I am using if rather than when.
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'.
Below is what I wrote:
DATA CLEANDATA36; SET CERT.INPUT36; GROUP=UPCASE(GROUP); WHERE GROUP IN ('A', 'B'); RUN; PROC MEANS DATA=CLEANDATA36 MEDIAN; VAR KILOGRAMS; CLASS GROUP;
RUN;I get it wrong but change the where to IF and get it correct can anyone help me understand this.
While the code works the question is how many observations are there after this step, the correct answer being 4992. With the where clause I get 4897.
Using the where clause, the condition will be applied when reading the dataset.
Therefore, upcase is not applied to this condition.
If you use the if clause, the condition will be evaluated as described after the dataset is loaded.
In other words, upcase will be applied.
The input36 dataset has 95 observations that contain the values "a" or "b" in lowercase, so there is a difference in the results between where and if.
WHERE filters the data set before it's read in. If you had a value of "a" in the original data set it wouldn't get filtered out.
Best way to figure this is out is to test it!
data group_data;
input group $;
cards;
a
A
a
b
a
a
C
B
c
d
a
G
B
A
A
;;;;
run;
title 'Example WHERE';
data example1;
set group_data;
group = upcase(group);
where group in ('A', 'B');
run;
proc print data=example1;run;
title 'Example IF';
data example2;
set group_data;
group = upcase(group);
IF group in ('A', 'B');
run;
proc print data=example2;run;
And notice the output is different!!!
WHERE:
Example WHERE Obs group 1 A 2 B 3 B 4 A 5 A
IF:
Obs group
1 A
2 A
3 A
4 B
5 A
6 A
7 B
8 A
9 B
10 A
11 A
@JC411911 wrote:
Hello,
I am preparing for the base certification and hoping I can get some help understanding this question I have gotten wrong a few times. The first step is a pretty basic ask but I got it wrong because I am using if rather than when.
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'.
Below is what I wrote:
DATA CLEANDATA36; SET CERT.INPUT36; GROUP=UPCASE(GROUP); WHERE GROUP IN ('A', 'B'); RUN; PROC MEANS DATA=CLEANDATA36 MEDIAN; VAR KILOGRAMS; CLASS GROUP;
RUN;I get it wrong but change the where to IF and get it correct can anyone help me understand this.
While the code works the question is how many observations are there after this step, the correct answer being 4992. With the where clause I get 4897.
Thank you @Reeza for your time and explanation with this. Definitely understand now!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.