BookmarkSubscribeRSS Feed
Obsidian | Level 7

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 
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; 
Super User

For those of use that do not use Oracle you could describe what INSTR and NVL do.

Community Manager

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.

Opal | Level 21

So you have a SAS SQL passthru query that works fine on Oracle and you HAVE to translate it into SAS SQL. Why? 



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1 like
  • 5 in conversation