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 |
You really need to provide some example data that demonstrates the issue that we can examine. https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... shows how to create data step code from a data set so that we can recreate the data for testing. You need to only include the variables of interest.
My first guess is that the code that appears to work with the "datetime" variable does not in fact involve a datetime variable but a date value whose name just implies a datetime. Possibly it has been reduced to a date using datepart() somewhere along the line.
Since the code is dependent upon the date the program is run then weekday(date())=2 is possibly a major player.
No problem. Here's 10 records from the first and second query.
QUERY1
data WORK.QUERY1;
infile datalines dsd truncover;
input ID:32. REQ_CRTE_DTTM:DATETIME26.6 TEST:$5.;
datalines4;
1,19JAN2017:09:12:45.021000,TRUE
2,19JAN2017:09:48:00.661000,TRUE
3,19JAN2017:11:38:54.764000,TRUE
4,19JAN2017:07:52:45.046000,TRUE
5,19JAN2017:07:49:27.994000,TRUE
6,19JAN2017:10:17:39.616000,TRUE
7,19JAN2017:08:00:03.429000,TRUE
8,19JAN2017:11:39:59.264000,TRUE
9,19JAN2017:02:51:26.049000,TRUE
10,19JAN2017:12:43:52.608000,TRUE
;;;;
QUERY2
data WORK.QUERY2;
infile datalines dsd truncover;
input Record:32. REQ_CRTE_DTTM:DATETIME26.6 TEST:$5. TEST2:$5.;
datalines4;
1,19JAN2017:09:12:45.021000,TRUE,FALSE
2,19JAN2017:09:48:00.661000,TRUE,FALSE
3,19JAN2017:11:38:54.764000,TRUE,FALSE
4,19JAN2017:07:52:45.046000,TRUE,FALSE
5,19JAN2017:07:49:27.994000,TRUE,FALSE
6,19JAN2017:10:17:39.616000,TRUE,FALSE
7,19JAN2017:08:00:03.429000,TRUE,FALSE
8,19JAN2017:11:39:59.264000,TRUE,FALSE
9,19JAN2017:02:51:26.049000,TRUE,FALSE
10,19JAN2017:12:43:52.608000,TRUE,FALSE
;;;;
Your sample data doesn't match the input data in the queries, so it can't be executed as is.
After modifying Query I get FALSE when you stated TRUE in your sample...?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.