topic Re: Where Condition on Case When variable values in SAS Programming

Where Condition on Case When variable values

buechler66 — Mon, 19 Sep 2016 16:04:59 GMT

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;

Re: Where Condition on Case When variable values

ballardw — Mon, 19 Sep 2016 16:21:05 GMT

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.

Re: Where Condition on Case When variable values

buechler66 — Mon, 19 Sep 2016 19:31:17 GMT

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???

Re: Where Condition on Case When variable values

ballardw — Mon, 19 Sep 2016 19:54:55 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Where Condition on Case When variable values

buechler66 — Mon, 19 Sep 2016 20:28:09 GMT

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.

Re: Where Condition on Case When variable values

Reeza — Mon, 19 Sep 2016 21:28:16 GMT

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?