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

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

RAW_newbie — Tue, 14 Aug 2018 16:40:31 GMT

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?

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

Reeza — Tue, 14 Aug 2018 17:38:38 GMT

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?

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

ChrisNZ — Tue, 14 Aug 2018 22:15:38 GMT

%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

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

RAW_newbie — Fri, 17 Aug 2018 14:14:28 GMT

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.