Interact, learn and grow

Join Now
Reply
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
Highlighted
SAS Employee
Posts: 20
Juleptip #7 SQL Datetime constant

Some of you may have been struggling with submitting SQL pass-through code containing a datetime constant.

When submitting implicit SQL, the SAS/ACCESS engine will convert your SAS datetime constant into the the native form for the database connection, but sometimes you want to submit native SQL to your database, it could be that you already had the code created elsewhere or for some other (good) reason forcing you to submit native SQL.

 

This tip shows how to convert a SAS datetime constant into a SQL datetime constant.

From the documentation I have been looking at for the database systems, it seems there a two variants of a datetime constant:

'YYYY-MM-DDTHH:MM: SS.fffff' or 'YYYY-MM-DD HH:MM: SS.fffff'

(I had to insert a space between the : and S to prevent smileys appearing in the constant Smiley Very Happy)

So basically the difference between the two is the T character inserted after the date.

 

First we define a macro that creates a macro variable containing the SQL Datetime constant.

 

 

/***********************************************************************************
 * Define a macro that converts the datetime value to ISO 8601 format.
 * Must be done in a data step, using the macro function %sysfunc removes 
 * fractions of a second when specified.
 * 
 * Parameters:
 * 
 * DateTimeValue  The datetime constant/value
 * NewVar         The macro variable that will contain the formatted datetime value
 * RemoveT        Yes: Remove the T character from the formatted datetime value
 *                No:  Leave the T character in the datatime value (default)
 ***********************************************************************************/
%macro SQLDateTime(DateTimeValue,NewVar,RemoveT=No);
  %global &NewVar;
  %let RemoveT = %upcase(&RemoveT);
  %if "&RemoveT" ne "YES" %then %let RemoveT=NO;
  data _null_;
    SQLDateTimeValue = put(&DateTimeValue,e8601dt25.3);
  %if &RemoveT = YES %then %do;
    SQLDateTimeValue = translate(SQLDateTimeValue,' ','T');
  %end;
    call symputx("&NewVar",SQLDateTimeValue);
  run;
%mend;

 

 

The macro has the parameter RemoveT, which must be set to Yes if you want a constant value without the T character.

Next we create the two SQL datetime constant we need for the pass-through code, enable the sastrace so we are able to see what SQL code is sent to the database.

 

 

%SQLDateTime('01Jan2016:12:00:00.000'dt,SQLDateTimeFrom,RemoveT=No)
%SQLDateTime('30Nov2016:23:59:59.999'dt,SQLDateTimeTo)
%put SQLDateTimeFrom = &SQLDateTimeFrom  SQLDateTimeTo = &SQLDateTimeTo;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql noprint;
  connect to odbc (noprompt="Driver={SQL Server};Trusted_Connection=Yes;Server=KOMIKW72\SQLEXPRESS;DATABASE=Sample;");
  create table test as
    select *
    from connection to odbc
    (select *
     from dbo.type2data
     where Valid_From_DTTM <= %tslit(&SQLDateTimeFrom) 
       and %tslit(&SQLDateTimeTo) < Valid_To_DTTM
    );
quit;

 

And finally the log showing the result.

 

25   %SQLDateTime('01Jan2016:12:00:00.000'dt,SQLDateTimeFrom,RemoveT=No)

NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


26   %SQLDateTime('30Nov2016:23:59:59.999'dt,SQLDateTimeTo)

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


27   %put SQLDateTimeFrom = &SQLDateTimeFrom  SQLDateTimeTo = &SQLDateTimeTo;
SQLDateTimeFrom = 2016-01-01T12:00:00.000  SQLDateTimeTo = 2016-11-30T23:59:59.999
28
29   options sastrace=',,,d' sastraceloc=saslog nostsuffix;
30   proc sql noprint;
31     connect to odbc
31 ! (noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
31 ! XXXXXX);
ODBC: AUTOCOMMIT is YES for connection 0
32     create table test as
33       select *
34       from connection to odbc
35       (select *
36        from dbo.type2data
37        where Valid_From_DTTM <= %tslit(&SQLDateTimeFrom)
38          and %tslit(&SQLDateTimeTo) < Valid_To_DTTM
39       );

ODBC_1: Prepared: on connection 0
select * from dbo.type2data where Valid_From_DTTM <= '2016-01-01T12:00:00.000' and
'2016-11-30T23:59:59.999' < Valid_To_DTTM


ODBC_2: Executed: on connection 0
Prepared statement ODBC_1

NOTE: Table WORK.TEST created, with 40 rows and 8 columns.

40   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.22 seconds
      cpu time            0.06 seconds


As you can tell from the log, the datetime constants are passed on to the database and the query is executed.

The above code was submitted to SQL Server 2014 from SAS 9.4M3.

This technique can also be used with the EXECUTE statement for PROC SQL.

 

Happy holidays!