topic Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format? in ODS and Base Reporting

Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Mgarret — Wed, 15 Jul 2015 15:38:26 GMT

Hi,

I have two questions in regard to pulling data through Oracle using Proc SQL :

I have a query which pulls through Oracle and I wanted to use the INTNX function to make the data selection process easier but unfortunately it won’t work in Oracle. Is there a work around?

intnx('MONTH', &end, -19, 'B') and intnx('MONTH', &end, -1, 'E')

2. I’m having to always change the date formats to SAS formats after I run a query in Oracle. Is there a way to do this within the first pass?

Below is my query. Note the macro var end is in a format Oracle understands.

%let end='01Jun2015';

proc sql;

connect to oracle (user=usdm password=passdm path='A7P5OLKL');

create table T_Hist as

select * from connection to oracle (

select distinct

t.customer_id,
t.web_order_id,
a.original_purchase_date,
t.transaction_date,
t.amount

from mbs.sas_transaction_vw t

left join mbs.sas_customer_bu_vw a

on t.customer_id = a.customer_id and a.business_unit = 3

where original_purchase_date between intnx('MONTH', &end, -19, 'B') and intnx('MONTH', &end, -1, 'E') /*Does not work*/

and t.business_unit = 3

and t.transaction_type = 1 and t.transaction_sub_type = 1

and t.customer_id not in (select distinct customer_id from mbs.sas_customer_vw

where regexp_like(nvl(suppress_reasons,'*'),'[EF]')));

disconnect from oracle;quit;

/* Change date var*/

data T_Hist(rename=(original_purchase_date_new=original_purchase_date TRANSACTION_DATE_new=Transaction_date));

set T_Hist;

original_purchase_date_new = datepart(original_purchase_date);

TRANSACTION_DATE_new=datepart(TRANSACTION_DATE);

format original_purchase_date_new TRANSACTION_DATE_new date9.;

drop original_purchase_date TRANSACTION_DATE; run;

Thanks!!

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

dcruik — Wed, 15 Jul 2015 15:50:24 GMT

I would recommend creating two additional macro variables for your original_purchase_date Where filter instead of using the intnx functions in the statement. That way you can just use something like "where original_purchase_date between "&start"d and "&end"d" instead of using the functions in the where statement.

As for the date formats coming through Oracle, you can always add a format statement in the sql procedure when pulling them. Try changing your date variables to the following:

datepart(a.original_purchase_date) as original_purchase_date format=date9.,

datepart(t.transaction_date) as transaction_date format=date9.,

Hope that helps!

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Mgarret — Wed, 15 Jul 2015 16:01:06 GMT

Hi dcruik,

---Since my start data is always 19 months after end date I just wanted to use one macro var so I would not have to calculate the minus 19 months date every time

-- I've tried using the datepart function it does not work when you pull through Oracle. This is the error I get.

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement:

select distinct t.customer_id, t.web_order_id, datepart(a.original_purchase_date) as

original_purchase_date format=date9., datepart(t.transaction_date) as transaction_date

format=date9., t.ip_ciem_label1, t.amount from mbs.sas_transaction_vw t left join

mbs.sas_customer_bu_vw a on t.customer_id = a.customer_id and a.business_unit = 3 where

t.transaction_date >= '01Dec2013' and t.transaction_date <= '01Jun2015' and

t.business_unit = 3 and t.transaction_type = 1 and t.transaction_sub_type = 1 and

t.customer_id not in (select distinct customer_id from mbs.sas_customer_vw where

regexp_like(nvl(suppress_reasons,'*'),'[EF]')).

1483 disconnect from oracle;

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

dcruik — Wed, 15 Jul 2015 16:11:57 GMT

That's very strange because I also pull from an Oracle database and use the datepart function when pulling datetime variables and usually have no issue. The Error message looks like it doesn't like something in your From statement however. The From statement looks fine and isn't being masked by a comma. Typically, when I filter on a date value, I use a date literal (i.e. '01Dec2013'd, '01Jun2015'd), or if the variables is a datetime value, I will use a datetime literal (i.e. '01Dec2013:00:00:00'dt, '01Jun2015:00:00:00'dt).

Is there a part of the code in the log that is underlined by the ERROR message?

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Mgarret — Wed, 15 Jul 2015 16:37:42 GMT

Yes, the error is not the from statement. The error is a result of this:

datepart(a.original_purchase_date) as original_purchase_date format=date9.,

datepart(t.transaction_date) as transaction_date format=date9.,

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Mgarret — Wed, 15 Jul 2015 16:40:01 GMT

And this is how a formatted the macro vars:

%let oend='01Jun2015';

%let ostart='01Dec2013';

But again I would rather set up the logic so I could just subtract 19 months from the start date.

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

dcruik — Wed, 15 Jul 2015 16:56:27 GMT

So try using this for your macro variables:

%let date=01Jun2015;

%let ostart=%sysfunc(intnx(month,"&date"d,-19,B),date9.);

%let oend=%sysfunc(intnx(month,"&date"d,-1,E),date9.);

Then when you call them, I would call them like this: original_purchase_date between "&ostart"d and "&oend"d.

For the datepart() function, I don't believe SQL Language knows what it is, and since you're connecting into the Oracle database, you probably can't use it. I do a differen't way of pulling my data from Oracle (through a libname statement). I would look into the convert() function in SQL and see if you can try something that way, I'm not exactly sure if SAS will like that or not, but I know that's a SQL language way to convert() variables.

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Tom — Wed, 15 Jul 2015 18:48:21 GMT

If you are going to push code into Oracle then you need to use Oracle code.

%let end=01JUN2015 ;

%let oracle_start=%sysfunc(dequote("todate('%sysfunc(intnx(MONTH, "&end"d , -19, B),date9)','ddmonyyy')")) ;

%let oracle_end=%sysfunc(dequote("todate('%sysfunc(intnx(MONTH, "&end"d , -1, E),date9)','ddmonyyy')")) ;

...

where original_purchase_date between &oracle_start and &oracle_end

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Mgarret — Thu, 16 Jul 2015 13:52:23 GMT

Hi Tom,

I tried to use your code and I'm still getting error

ERROR: ORACLE prepare error: ORA-00904: "TODATE": invalid identifier. SQL statement: select

distinct t.customer_id, t.web_order_id, a.original_purchase_date, t.transaction_date,

t.amount from mbs.sas_transaction_vw t left join mbs.sas_customer_bu_vw a on

t.customer_id = a.customer_id and a.business_unit = 3 where t.transaction_date between

todate('01NOV2013','ddmonyyy') and todate('31MAY2015','ddmonyyy') and t.business_unit =

3 and t.transaction_type = 1 and t.transaction_sub_type = 1 and t.customer_id not in

(select distinct customer_id from mbs.sas_customer_vw where

regexp_like(nvl(suppress_reasons,'*'),'[EF]')).

proc sql;

connect to oracle (user=usdm password=passdm path='A7P5OLKL')

create table T_Hist as

select * from connection to oracle (

select distinct

t.customer_id,

t.web_order_id,

a.original_purchase_date,

t.transaction_date,

t.amount

from mbs.sas_transaction_vw t

left join mbs.sas_customer_bu_vw a

on t.customer_id = a.customer_id and a.business_unit = 3

where t.transaction_date between &oracle_start and &oracle_end

and t.business_unit = 3

and t.transaction_type = 1 and t.transaction_sub_type = 1

and t.customer_id not in (select distinct customer_id from mbs.sas_customer_vw

where regexp_like(nvl(suppress_reasons,'*'),'[EF]')));

disconnect from oracle;quit;

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Mgarret — Thu, 16 Jul 2015 13:55:27 GMT

Actually, I just changed "todate" to "to_date" and I get this error.

ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire

input string.

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

RW9 — Thu, 16 Jul 2015 13:55:47 GMT

Try replacing todate with to_date():

Oracle/PLSQL: TO_DATE Function

And the mask should be four Y's:

dequote("to_date('%sysfunc(intnx(MONTH, "&end"d , -19, B),date9)','ddmonyyyy')")) ;

^ ^

Re: Passing Date Ranges through Oracle: Substitution for INTNX? Changing Date Format to SAS Date Format?

Tom — Thu, 16 Jul 2015 14:06:59 GMT

The macro code is just generating text string.

Debug the code that you want to get to work on your Oracle connection by typing examples. Then make the macro code generate the code that works.

TO_DATE('01JAN1960','ddmonyyyy')