topic Where statements with "and" in SAS Procedures

Where statements with "and"

rapt1 — Wed, 18 Aug 2021 13:18:51 GMT

Hi guys,

My data tables has about 1,000 records with the following variables:

Name

Age

City

State

MonthlyBill

I am using the following code to ~~remove~~ retain those who are from NY and pays monthly of $500 or less

proc sql;
create table want as
select t1.*
from have t1
where t1.state IN ('NY') 
and t1.MonthlyBill lt 500;
quit;

I am left with 300 records and I am confused. Upon checking, my tables have about 200 NY customers and 500 are those below 500 bill. I was only expecting about 200 to be removed.

Any thoughts?

Re: Where statements with "and"

Tom — Wed, 18 Aug 2021 13:10:59 GMT

Your description is confusing. Your code is not "removing" anything. It is selecting just the observations that meet both criteria.

So if there are only 200 NY records then the maximum number of observations you will get is 200. If any of them have bills larger than 500 then you will get less than 200. Remember that SAS will treat missing values as less than any actual number so your WANT dataset could include observations with missing values for monthlybill

Re: Where statements with "and"

rapt1 — Wed, 18 Aug 2021 13:25:55 GMT

Oh. Let me revisit my code. Thanks!