topic Re: WHERE/IF UPCASE() in New SAS User

WHERE/IF UPCASE()

JC411911 — Fri, 04 Jun 2021 03:15:36 GMT

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.

Re: WHERE/IF UPCASE()

japelin — Fri, 04 Jun 2021 03:36:25 GMT

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.

Re: WHERE/IF UPCASE()

Reeza — Fri, 04 Jun 2021 03:38:10 GMT

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.

Re: WHERE/IF UPCASE()

JC411911 — Fri, 04 Jun 2021 22:07:51 GMT

Thank you @Reeza for your time and explanation with this. Definitely understand now!

Re: WHERE/IF UPCASE()

JC411911 — Fri, 04 Jun 2021 22:09:38 GMT

Thank you @japelin !