BookmarkSubscribeRSS Feed
buechler66
Barite | Level 11

 

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;
 

 

5 REPLIES 5
ballardw
Super User

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.

buechler66
Barite | Level 11

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

ballardw
Super User

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.

buechler66
Barite | Level 11
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.
Reeza
Super User

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1033 views
  • 0 likes
  • 3 in conversation