BookmarkSubscribeRSS Feed
Mgarret
Obsidian | Level 7

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!!

11 REPLIES 11
dcruik
Lapis Lazuli | Level 10

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!

Mgarret
Obsidian | Level 7

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;

dcruik
Lapis Lazuli | Level 10

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?

Mgarret
Obsidian | Level 7

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

Mgarret
Obsidian | Level 7

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.

dcruik
Lapis Lazuli | Level 10

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.

Tom
Super User Tom
Super User

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



Mgarret
Obsidian | Level 7

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

                     ^                                                                                                                                      ^

Mgarret
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 4899 views
  • 0 likes
  • 4 in conversation