Desktop productivity for business analysts and programmers

Date v DateTime used Interchangeably Not Creating Error

Reply
Occasional Contributor
Posts: 14

Date v DateTime used Interchangeably Not Creating Error

[ Edited ]

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_IDTESTTEST2
18505TRUEFALSE
Super User
Posts: 5,386

Re: Date v DateTime used Interchangeably Not Creating Error

I don't have SAS at my fingertips right now to try this logic out.
But isn't bullet proof. A datetime in 1960 could pas as date.
Modelling rule no 1: never mix data types in the same column.
Data never sleeps
Super User
Posts: 11,134

Re: Date v DateTime used Interchangeably Not Creating Error

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.

Occasional Contributor
Posts: 14

Re: Date v DateTime used Interchangeably Not Creating Error

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
;;;;
Super User
Posts: 5,386

Re: Date v DateTime used Interchangeably Not Creating Error

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

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 170 views
  • 0 likes
  • 3 in conversation