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;
For those of use that do not use Oracle you could describe what INSTR and NVL do.
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.
So you have a SAS SQL passthru query that works fine on Oracle and you HAVE to translate it into SAS SQL. Why?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.