- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 07-26-2016 12:22 PM
(1905 views)
I’m trying to translate an oracle query to proc sql. I am still pull through a connection to an oracle environment but I have to use proc sql in SAS. There is a section in the query I am having trouble translation.
I don't think I can use the Instr and NVL within a proc sql statement.
Any assistance will be greatly appreciated.
proc sql; connect to oracle (user=polodm password=polodm path='K6PPOLDM'); create table CUSTOMER_TRANS as select * from connection to oracle ( select distinct cu.mstr_customer_id, t.customer_id from mbs.sas_transaction_vw t inner join mbs.sas_customer_vw cu on cu.customer_id = t.customer_id where t.transaction_date >= '01AUG2014' and t.transaction_date <= '30AUG2014' and t.transaction_type = 1 and t.business_unit IN (1,3) and not in ( select customer_id from mbs.sas_customer_bu_vw where business_unit = 3 and Instr(substr(rtm_bulk_flags,1,3),'Y') > 0 or Instr(NVL(suppress_reasons, ' '),'E') > 0 /*Employees*/ or Instr(NVL(suppress_reasons, ' '),'F') > 0 /*Fraud*/) and business_unit in (1,3) and customer_id = t.customer_id) disconnect from oracle;quit;
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
For those of use that do not use Oracle you could describe what INSTR and NVL do.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I think you might just want the FIND function (if working with SAS data) or the INDEXC function is an old-school method that could work too.
Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So you have a SAS SQL passthru query that works fine on Oracle and you HAVE to translate it into SAS SQL. Why?