SAS Procedures

Help using Base SAS procedures
BookmarkSubscribeRSS Feed
RobertNYC
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 
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
ballardw
Super User

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

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

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!
SASKiwi
PROC Star

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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