DATA Step, Macro, Functions and more

Where Condition on Case When variable values

Reply
Regular Contributor
Posts: 212

Where Condition on Case When variable values

[ Edited ]

 

I'm having trouble with my Where condition:

 

  and a.Rule_Order is not null;

 

Whenever I add this condition to my query no rows are returned. I've verified that there are Rule_Order values of 1.0, 1.5, and . in the SAS dataset when I exclude this condition. So this leaves me to believe it has something to do with my Case When statement processing.

 

Can anyone see what it is I'm doing wrong?

 

proc sql;
create table QueryData00926 as
select DISTINCT
     a.actual_dlvry_date as ad_dt,
     a.imb_code length = 31,
     a.imb_dlvry_zip_5,
     CASE
           WHEN (A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ) THEN 1.0
           WHEN (A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE ) THEN 1.5
     ELSE .
     END as Rule_Order length = 5 format=4.1
from QueryDataBase00926 as a
inner join QueryDataBase&ZIP5 as b
on a.imb_code=b.imb_code
where a.source='A' and b.source='B' 
and a.Rule_Order is not null; quit;
 

 

Super User
Posts: 11,343

Re: Where Condition on Case When variable values

Posted in reply to buechler66

Is this a pass through to another database?

If not you may mean

missing (A.ACTUAL_DLVRY_DATE) AND not missing(B.ACTUAL_DLVRY_DATE)

 

SAS doesn't use NULL.

Regular Contributor
Posts: 212

Re: Where Condition on Case When variable values

Hmm, I tried this too, but got the same result.

 

I think since Rule_Order exists as missing (.) on both tables for all records, that during the merge the Where condition is only seeing those missing values and not the recoded Rule_Order values from the Case When condtions...does that make sense?  It's like the newly created Rule_Order column from the Case When condtions can't be seen by the Where condition or something???

Super User
Posts: 11,343

Re: Where Condition on Case When variable values

Posted in reply to buechler66

Also need to modify this statement to get rid of the the Null:

 

and a.Rule_Order is not null

 

A few rows of data may help. https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... has instruction on created text of a data step you could post here to provide example data to run code against. You would only need to provide enough rows to exercise the the code involved and only the variables mentioned.

Regular Contributor
Posts: 212

Re: Where Condition on Case When variable values

Yah, I tried it as 'and a.rule_order is not missing', but got the same thing.

Yes, I'll try to reproduce with some simple data.
Super User
Posts: 19,855

Re: Where Condition on Case When variable values

Posted in reply to buechler66

Why is it a.Rule_Order when you're calculating it in the step? Is there a variable called Rule_Order in your Table A that differs from the calculated value?

Ask a Question
Discussion stats
  • 5 replies
  • 366 views
  • 0 likes
  • 3 in conversation