SAS where statement

Question

SAS newbie - trying to complete a practice exercise. I'm probably going to face palm myself once someone points out what I'm doing wrong, but for now, I can't tell what the issue is.

I have a datset with 3 variables: ID $ avgNumDonations DonationAmt.

I'm asked to create a subset (i'm doing it in my proc print statement) that contains no records with avgDonation below 20 and DonationAmt under a million.(I believe this is a trick question as there are no cases in the original data set that meet both criteria)

I wrote my where clause as follows: where DonationAmt >= 1000000 and avgNumDonations >= 20

However, it seems to be acting as an OR statement instead of a AND statement, because my subset is eliminating ID's 45 and 78.

Can someone tell me what I'm missing? As I mentioned, no cases meet the criteria so I expected to have the same cases in my "subset".

Answer 1

I think you may be misunderstanding either the WHERE or AND/OR logic.

WHERE is an inclusion criteria. Almost all of your records meet this criteria, but not all. Note that with AND it does have to meet both of your criteria, if either is false the it is excluded. It sounds like you want an OR instead of AND.

So to determine records that are excluded, either criteria would be false. So look for records where numDonations < 20 - (ID 45) and DonationAmount< 1000000 - ID 78. So those two records would be excluded. Which is what you're seeing.

Answer 2

If both criterias should meet the conditions you have to use OR instead of AND:

data a;
  id=12;
  avgdon = 58.3;
  sumdon=4833722;
    output;
  id=45;
  avgdon = 15.3;
  sumdon=14833722;
    output;
  id=56;
  avgdon = 50.3;
  sumdon=9833722;
    output;
  id=78;
  avgdon = 39.3;
  sumdon=833722;
    output;
  id=910;
  avgdon = 28.3;
  sumdon=2833722;
    output;
run;

proc print data=a(where=(sumdon>=1000000 OR avgdon>=20));
run;

Otherwise it is correct to use AND. Then 2 rows are eliminated.