topic Re: Why is my WHERE statement ignored in New SAS User

Why is my WHERE statement ignored

cwarfield — Sun, 18 Apr 2021 18:07:02 GMT

After running this code, I am seeing all basins, not just NA or na. What am I doing wrong? It just doesn't make sense to me that this would be ignored and there is no error to help me see why.

***********************************************************;
* Activity 3.06 *;
* 1) Modify the OUT= option in the PROC SORT statement *;
* to create a temporary table named STORM_SORT. *;
* 2) Complete the WHERE and BY statements to answer *;
* the following question: Which storm in the North *;
* Atlantic basin (NA or na) had the strongest *;
* MaxWindMPH? *;
***********************************************************;

proc sort data=pg1.storm_summary out=storm_sort;
where Basin = 'NA' or 'na';
by descending MaxWindMPH;
run;

Re: Why is my WHERE statement ignored

PaigeMiller — Sun, 18 Apr 2021 18:12:12 GMT

Your syntax is wrong.

where Basin = 'NA' or 'na';

should be

where Basin = 'NA' or basin='na';

From now on, when your code isn't working, show us the LOG so we can see the code along with NOTEs, ERRORs and WARNINGs that appear in the log.

Re: Why is my WHERE statement ignored

cwarfield — Sun, 18 Apr 2021 18:14:30 GMT

I've added the log, there are no warnings or errors. Basin is a character value, it is NA, na, SP, etc. There are a couple other options. All of them show in the output data so the where statement is being ignored.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc sort data=pg1.storm_summary out=storm_sort;

74 where Basin='NA' or 'na';

75 by descending MaxWindMPH;

76 run;

NOTE: There were 3118 observations read from the data set PG1.STORM_SUMMARY.

WHERE 1 /* an obviously TRUE WHERE clause */ ;

NOTE: The data set WORK.STORM_SORT has 3118 observations and 12 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2517.15k

OS Memory 32060.00k

Timestamp 04/18/2021 06:12:26 PM

Step Count 83 Switch Count 5

Page Faults 0

Page Reclaims 296

Page Swaps 0

Voluntary Context Switches 23

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 784

78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Why is my WHERE statement ignored

cwarfield — Sun, 18 Apr 2021 18:15:53 GMT

Thank you. I see now that the variable has to be listed again for the boolean operator to apply for a where statement.

Re: Why is my WHERE statement ignored

ballardw — Sun, 18 Apr 2021 22:21:10 GMT

Another approach when comparing a single variable to a list of values is to use the IN operator:

Where basin in ('NA' 'na');

which is the same as multiple 'or'.

Another considering the difference between the values listed to make the case consistent:

Where upcase (basin) = 'NA';

Which makes the value compared to the list all upper case. If you actually have other case differences like "nA" or "Na" this might be preferred.

Re: Why is my WHERE statement ignored

Manal02 — Tue, 08 Feb 2022 21:52:23 GMT

You need to whrite the code this way :

proc sort data=pg1.storm_summary out=storm_sort;
where Basin in("NA" "na");
by descending MaxWindMPH;
run;

or you can just modify the where instructions as: where Basin= 'NA' or Basin= 'na'

Re: Why is my WHERE statement ignored

Tom — Tue, 08 Feb 2022 22:16:01 GMT

Because the non empty string 'na' is considered TRUE by SAS.

So your where condition reduces to WHERE (test1) or TRUE. Which is always going to be TRUE.