09-19-2016 12:03 PM - edited 09-19-2016 12:04 PM
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;
09-19-2016 12:21 PM
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.
09-19-2016 03:31 PM
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???
09-19-2016 03:54 PM
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.
09-19-2016 05:28 PM
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?