topic Re: upcase function and subsetting in SAS Programming

upcase function and subsetting

melc — Mon, 12 Oct 2020 22:05:10 GMT

A question in the SAS Certification Practice Programming Performance-Based Exam, requires the use of the upcase function under the condition of subsetting the data set with the values of 'A' or 'B' from the variable called group. A part of the question is to find the number of observations that result from the program. I have listed some programs below and I would like to know why program #3 does not give the same results as #1, #2, and #4.

The answer that SAS provided which results in 4992 observations:
data cleandata;
set cert.input36;
if upcase(group) in('A', 'B');
run;
It appears that instead of using 'if', 'where' can provide the same result of 4992 observations:
data cleandata;
set cert.input36;
where upcase(group) in('A', 'B');run;
I thought the following program would result in the correct answer but it doesn't. Why does the following not provide the same results as the two programs above? This has fewer observations than the two programs above. The following results in 4897 observations.
data cleandata;
set cert.input36;
group=upcase(group);
where group in ('A', 'B');
run;
The following program results in the same number of observations (4992 observations) as the first two programs but it seems to defeat the purpose of using the upcase function because the upcase function can be removed to give the same result

data cleandata;
set cert.input36;
group=upcase(group);
where group in ('A', 'B', 'a', 'b');
run;

OR remove the upcase function since listing all of the possible values with the in operator (4992 observations)

data cleandata;
set cert.input36;
where group in ('A', 'B', 'a', 'b');
run;

Re: upcase function and subsetting

FreelanceReinh — Mon, 12 Oct 2020 22:35:00 GMT

Hello @melc and welcome to the SAS Support Communities!

The WHERE statement filters the incoming data immediately (see the note in the log saying "There were ... observations read from data set ... WHERE ..." and also the documentation), i.e., other DATA step statements such as the assignment statement group=... in the third and fourth program already operate on the subset selected by the WHERE condition (using the GROUP values found in the input dataset), regardless of their position in the DATA step code. Hence, the UPCASE function call in programs 3 and 4 comes too late for the WHERE statement and affects only the outgoing data written to dataset CLEANDATA.

Re: upcase function and subsetting

melc — Mon, 12 Oct 2020 22:53:40 GMT

I understand now. Thanks!