Experiencing this with 5.1 (5.100.0.13304) Hot fix 17 (32-bit).
A co-worker wrote a query using a Between date filter. I noticed that Date and DateTime values were being used interchangeably in the filter and it seemed to be working correctly. The field is a DateTime and the logic in the Case statement uses Date values. Here's the filter:
WHERE t3.REQ_CRTE_DTTM BETWEEN case when weekday(date())=2 then date()-3 else
date()-1 end AND Date()+4
Out of curiosity, I created a computed column that would return a True/False using the same logic as the filter.
/* TEST */
(case
when t3.REQ_CRTE_DTTM BETWEEN case when weekday(date())=2 then date()-3 else date()-1 end AND Date()+4 then
"TRUE"
else "FALSE"
end) LABEL="TEST" AS TEST
All of the results came back TRUE.
I then created a computed column on the returned dataset using the exact same logic:
/* TEST2 */
(case
when t1.REQ_CRTE_DTTM BETWEEN case when weekday(date())=2 then date()-3 else date()-1 end AND Date()+4 then
"TRUE"
else "FALSE"
end) LABEL="TEST2" AS TEST2
All of the results came back FALSE.
What am I missing here? How is the same logic returning two different results? I'm sure this is something simple or just that my understanding of Date and DateTime values in SAS is completely askew. Please enlighten me.
Here's the entirety of the code for context.
QUERY 1
%_eg_conditional_dropds(WORK.MIDEImport);
PROC SQL;
CREATE TABLE WORK.MIDEImport AS
SELECT t3.CRTE_BY_USR_ID,
t1.CE_DOC_ID AS DOC_ID,
t1.DOC_TYPE_CD AS DOC_TYPE_SHT_CD,
t1.DOC_SUBTYPE_CD AS DOC_SUBTYPE_SHT_CD,
t1.REQ_CRTE_DTTM,
t3.REQ_CRTE_DTTM AS CRTE_DTTM,
t3.JOB_PROF_REF_CD,
t3.REQ_ID,
t3.BUS_UNIT_SHT_CD,
/* RDD_DOCCAT */
(Cats(t1.DOC_TYPE_CD,t1.DOC_SUBTYPE_CD)) AS RDD_DOCCAT,
/* TEST */
(case
when t3.REQ_CRTE_DTTM BETWEEN case when weekday(date())=2 then date()-3 else date()-1 end AND Date()+4 then
"TRUE"
else "FALSE"
end) LABEL="TEST" AS TEST
FROM EIWDMIDE.IMG_REQ_DOC_DTLS t1
INNER JOIN EIWDMIDE.IMG_REQ_AGG t3 ON (t1.REQ_ID = t3.REQ_ID AND (t1.SRC_SYS_ID = t3.SRC_SYS_ID))
WHERE t3.REQ_TYPE_SHT_CD = 'Incmg' AND t3.REQ_CRTE_DTTM BETWEEN case when weekday(date())=2 then date()-3 else
date()-1 end AND Date()+4 AND t3.CRTE_BY_USR_ID NOT = 'P8P_GcdAdmin' AND t3.JOB_PROF_REF_CD IN
(
'ICISM',
'ICISW',
'ICLMP',
'ICLMS',
'ICLMW',
'ICMIT',
'ICOBL',
'ICOBN',
'ICOBS',
'ICOCM',
'ICOFM',
'ICOLS',
'ICOMG',
'ICOMI',
'ICORA',
'ICOWC',
'IUIDX',
'ICDAN',
'ICDAR',
'ICDAU',
'ICFCL',
'ICGBL'
);
QUIT;
QUERY 2
%_eg_conditional_dropds(WORK.QUERY_FOR_MIDEIMPORT_0001);
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_MIDEIMPORT_0001 AS
SELECT t1.CRTE_BY_USR_ID,
t1.DOC_ID,
t1.DOC_TYPE_SHT_CD,
t1.DOC_SUBTYPE_SHT_CD,
t1.REQ_CRTE_DTTM,
t1.CRTE_DTTM,
t1.JOB_PROF_REF_CD,
t1.REQ_ID,
t1.BUS_UNIT_SHT_CD,
t1.RDD_DOCCAT,
t1.TEST,
/* TEST2 */
(case
when t1.REQ_CRTE_DTTM BETWEEN case when weekday(date())=2 then date()-3 else date()-1 end AND Date()+4 then
"TRUE"
else "FALSE"
end) LABEL="TEST2" AS TEST2
FROM WORK.MIDEIMPORT t1;
QUIT;
A summary table of Query 2's results:
| COUNT_of_DOC_ID | TEST | TEST2 |
| 18505 | TRUE | FALSE |
Click image to register for webinar
