How to extract records in SAS PROC SQL passthru, filtered by date, but the date is in string format?

Question

I'm trying to extract data from date range (set by start_date and end_date variables defined in the null step).

Usually I would do this using passthru PROC SQL, as follows:

PROC SQL;
    CONNECT TO ORACLE AS xxxxx (AUTHDOMAIN="xxxxx" PATH=xxxxx preserve_comments);

    CREATE TABLE 
        work.new_data AS
    SELECT 
        *
    FROM 
        CONNECTION TO xxxxx (SELECT /*+parallel(16)*/ var1, var2, var3
        FROM
            oracle_data
        WHERE date >= &start_date. AND date <= &end_date.);

    DISCONNECT FROM xxxxx;
QUIT;

This extracts the data much more efficiently than doing it through a data step or pulling all the data and then filtering it.

The problem is with this particular dataset I'm using, the datetime is stored as a string in the format "DD/MM/YYYY HH:MM:SS". I know how to convert this normally in a data step or such, but the problem is I cannot convert it or interpret it as a date in the PROC SQL passthru stage.

Replacing the WHERE step with any SAS function like below throws an "Oracle Prepare Error" as it doesn't recognize the functions. I've also tried using SQL functions for something similar in the past and they also didn't work and I didn't manage to find a solution.

WHERE DATEPART(INPUT(rtp_date,anydtdtm.)) >= &start_date.)

Is it possible to interpret a string as datetime in the passthru stage and use it for filtering? Or is there perhaps another way to do this which is still more efficient than pulling everything or performing a data step directly onto the oracle data?

Answer 1

We use formats to simplify the process.

Create a format called oracledt. that takes a datetime value and converts it to the format 'mm/dd/yy hh:mm:ss' (including the quotes).

proc format lib=work;
  picture oracledt low-high = '''%0m/%0d/%y %0H:%0M:%0S''' (datatype = datetime) ;
run ;

Create a macro variable called my_datetime that contains the current datetime formatted with the above custom format:

%let my_datetime = %sysfunc(datetime(), oracledt.);
%put &=my_datetime;

Output:

MY_DATETIME='02/28/20 09:13:17'

This whitepaper describes the various values you can use when building your own custom format: http://www2.sas.com/proceedings/forum2007/026-2007.pdf

If the format you're after is 'yyyy-mm-dd hh:mm:ss' then your format definition would look like this: '''%Y-%0m-%0d %0H:%0M:%0S'''.

As Richard demonstrates, it is a good practice to keep your dates/datetimes stored as SAS dates/datetime values so that you can work with them, and then create additional variables to be used for the passthrough statement.

Answer 2

When you pass through date criteria to Oracle, one construct for the server side date literal is literally

DATE 'yyyy-mm-dd'

For the task of populating macro variables with source code that is Oracle source code for a date literal you will need to interpret your SAS datetime string, retrieve the date part and render that value as an Oracle date literal.

Example:

options nosource;

data have;
  length task start_date_string end_date_string $19;
  input task start_date_string&  end_date_string&;
datalines;
task1  31/01/2020 08:09:10  02/02/2020 11:00:00
task2  15/03/2019 02:00:00  19/03/2019 23:00:00
;

proc sql noprint;
  select start_date_string, end_date_string into :start_date, :end_date 
  from have where task='task1';

%put &=start_date;
%put &=end_date;

%let s_datepart_val = %sysfunc(inputn(&start_date,ddmmyy10.));
%let e_datepart_val = %sysfunc(inputn(&end_date,ddmmyy10.));

%put &=s_datepart_val;
%put &=e_datepart_val;

%let ora_start_literal = DATE %str(%')%sysfunc(putn(&s_datepart_val,yymmdd10.))%str(%');
%let ora_end_literal   = DATE %str(%')%sysfunc(putn(&e_datepart_val,yymmdd10.))%str(%');

%put &=ora_start_literal;
%put &=ora_end_literal;
---------- LOG ----------
START_DATE=31/01/2020 08:09:10
END_DATE=02/02/2020 11:00:00
S_DATEPART_VAL=21945
E_DATEPART_VAL=21947
ORA_START_LITERAL=DATE '2020-01-31'
ORA_END_LITERAL=DATE '2020-02-02'

And an alternate approach to populating the macro variables containing the date literals;

proc sql noprint;
  select
    'DATE ' || quote(put(input(start_date_string,ddmmyy10.),yymmdd10.),"'")
  , 'DATE ' || quote(put(input(  end_date_string,ddmmyy10.),yymmdd10.),"'")
  into
    :ora_start_literal
  , :ora_end_literal
  from
    have
  where
    task = 'task2'
  ;

%put &=ora_start_literal;
%put &=ora_end_literal;
---------- LOG ----------
ORA_START_LITERAL=DATE '2019-03-15'
ORA_END_LITERAL=DATE '2019-03-19'

The pass through would utilize the 'literal' macro variables

WHERE date >= &ora_start_literal. AND date <= &ora_end_literal;