DATES.... Using SAS date in proc sql "where" statement

[RAW_newbie]

I am passing a date range to a PROC SQL and attempting to use them in a where clause.  The query is against an Oracle database; the EFFECTIVEDATE comes in as date time format. 

If I supply the dates from within the SAS code:

%let Data_DT_MAX_EF= 18JUL2018; /*Used for testing*/

%let Data_DT_MIN_EF= 12JUL2018; /*Used for testing*/

And use them in the where clause:

EFFECTIVEDATE between &Data_DT_MIN_EF and &Data_DT_MAX_EF

There are no errors.

If I pass the dates from an EG program, the dates are passed between EGP and SAS however they are presented as numeric and the query fails.

Data work.Research_Date;

  Data_DT_MIN_EF = intnx('week1.5',&Report_Date3,-1,'End');

  Data_DT_MAX_EF = intnx('week1.5',&Report_Date3,-1,'Begin');

  format _all_ DATE9.;

  put (_all_)(=/);

  call symput ('Data_DT_MIN_EF', Data_DT_MIN_EF);

  call symput ('Data_DT_MAX_EF', Data_DT_MAX_EF);

run;

ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER. SQL statement: SELECT TRANID,...

where  EFFECTIVEDATE between 21383 and 21377

When and how is the best way to format the date variables to make the where clause successful?  

[Reeza]

Remember it needs to be valid SAS syntax and your code would resolve to:

*What you have;
EFFECTIVEDATE between &Data_DT_MIN_EF and &Data_DT_MAX_EF

*what sas sees;
EFFECTIVEDATE between 12Jul2018 and 18Jul2018

*valid SAS syntax;
EFFECTIVEDATE between "&Data_DT_MIN_EF"d and "&Data_DT_MAX_EF"d

If you're using SQL Pass through you'll need a different method, since they dates usually need to be formatted dd-mm-yy with single quotes or something like that. It varies slightly depending on the DB. 

---

@RAW_newbie wrote:

I am passing a date range to a PROC SQL and attempting to use them in a where clause.  The query is against an Oracle database; the EFFECTIVEDATE comes in as date time format. 

If I supply the dates from within the SAS code:

%let Data_DT_MAX_EF= 18JUL2018; /*Used for testing*/

%let Data_DT_MIN_EF= 12JUL2018; /*Used for testing*/

And use them in the where clause:

EFFECTIVEDATE between &Data_DT_MIN_EF and &Data_DT_MAX_EF

There are no errors.

If I pass the dates from an EG program, the dates are passed between EGP and SAS however they are presented as numeric and the query fails.

Data work.Research_Date;

  Data_DT_MIN_EF = intnx('week1.5',&Report_Date3,-1,'End');

  Data_DT_MAX_EF = intnx('week1.5',&Report_Date3,-1,'Begin');

  format _all_ DATE9.;

  put (_all_)(=/);

  call symput ('Data_DT_MIN_EF', Data_DT_MIN_EF);

  call symput ('Data_DT_MAX_EF', Data_DT_MAX_EF);

run;

ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER. SQL statement: SELECT TRANID,...

where  EFFECTIVEDATE between 21383 and 21377

When and how is the best way to format the date variables to make the where clause successful?  

---

[ChrisNZ]

%let Data_DT_MAX_EF= 18JUL2018; /*Used for testing*/
%let Data_DT_MIN_EF= 12JUL2018; /*Used for testing*/
And use them in the where clause:
EFFECTIVEDATE between &Data_DT_MIN_EF and &Data_DT_MAX_EF

This code would not work in SAS or in Oracle.

Oracle needs (note the single quotes which are missing in the code above):

EFFECTIVEDATE between '12JUL2018' and '18JUL2018'

SAS needs (single or double quotes are accepted in SAS, double quotes are better is macro variables are used):

EFFECTIVEDATE between "12JUL2018"d and "18JUL2018"d

[RAW_newbie · Accepted solution]

My failure was due to the formatting of the date being passed to the where clause.  Being new to converting dates I was missing the line in bold (middle of the 3 lines).  Currently a set of date intervals are developed within the EG program and passed to SAS base code with an "include" statement.  The same EGP loops through a dataset to supply a variable also supplied to the base code to produce 9 separate date dependent reports.

Data_DT_MIN_EF = intnx('week1.5',&Report_Date1,-1,'End');

Data_DT_MIN_EF2=put(Data_DT_MIN_EF,date9.);

call symput ('Data_DT_MIN_EF', Data_DT_MIN_EF2);

The SAS base code now interprets the dates correctly in the "where" clause which uses the EFFECTIVEDATE field within an Oracle table.

Where clause:

EFFECTIVEDATE between %bquote('&Data_DT_MAX_EF') and %bquote('&Data_DT_MIN_EF')

Quotes are supplied within the statement above to comply with Oracle needs.  The dates without quotes as supplied by the EGP statement are used elsewhere in the reports.