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