The SAS Output Delivery System and reporting techniques

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

Reply
Frequent Contributor
Posts: 142

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

Hi,

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

  1. 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

  1. t.customer_id,
  2. t.web_order_id,
  3. a.original_purchase_date,
  4. t.transaction_date,
  5. 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!!

Frequent Contributor
Posts: 128

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

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!

Frequent Contributor
Posts: 142

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

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;

Frequent Contributor
Posts: 128

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

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?

Frequent Contributor
Posts: 142

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

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.,

Frequent Contributor
Posts: 142

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

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.

Frequent Contributor
Posts: 128

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

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.

Super User
Super User
Posts: 6,309

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

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



Frequent Contributor
Posts: 142

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

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;

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

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

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')")) ;

                     ^                                                                                                                                      ^

Frequent Contributor
Posts: 142

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

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.

Super User
Super User
Posts: 6,309

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

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')

Ask a Question
Discussion stats
  • 11 replies
  • 1266 views
  • 0 likes
  • 4 in conversation