How to convert Oracle SQL code to SAS code

[QHO]

Dear all,

I'm having a trouble converting Power function and double Decode statement to SAS proc sql.  Please help me with 2 issues below:

1/ I would like to get the GROUP_ID to match with another table, but the source data is different from the destination, and I don't understand the previous person program as below:

POWER(2,33) * DECODE(IS_NUMBER(SUBSTR( LC.LAB_REFERENCE,1,5)),1,TO_NUMBER(SUBSTR( LC.LAB_REFERENCE,1,5)), NULL) +
DECODE( IS_NUMBER(SUBSTR( LC.LAB_REFERENCE,7, LENGTH( LC.LAB_REFERENCE)-6 - (DECODE(INSTR( LC.LAB_REFERENCE,'/R',1),0,0,3)))),
1, TO_NUMBER(SUBSTR( LC.LAB_REFERENCE,7, LENGTH( LC.LAB_REFERENCE)-6 - (DECODE(INSTR( LC.LAB_REFERENCE,'/R',1),0,0,3)))),
NULL) AS GROUP_ID

2/ This one has double DECODE function.  As I would like to create 2 columns for separate Left Len Cost vs. Right Len Cost. 

DECODE(DECODE(LC.ITEM_TYPE,'L',NVL(LC.ITEM_EYE,'B')),'L',TOTAL_COST) AS L_LENS_COST,
DECODE(DECODE(LC.ITEM_TYPE,'L',NVL(LC.ITEM_EYE,'B')),'R',TOTAL_COST) AS R_LENS_COST

Thank you in advance for your help.

[SASKiwi]

Why do you need to convert it? You can run this as is from SAS using SQL Passthru.

[QHO]

Thanks SASKiwi for your question.  I actually don't know how to use SQL Passthru.  Do you have an example?

I'm familiar with Proc sql 😞

Thanks again.

[SASKiwi]

Here is some sample code designed for SQL Server. Just replace it with your Oracle connection details. Place your unmodified Oracle SQL where it says "Put your SQL Server query here". 

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
  create table Want  as 
  select * from connection to odbc
  (---- Put your SQL Server query here
   )
  ;
  disconnect from odbc;
quit;

[QHO]

Thanks @SASKiwi 

I'm having another issue with connecting to the database using your sample code.  I've contacted our DBA, hopefully the server is down or my path is wrong.  Fingers crossed! 

PROC SQL;
29 CONNECT TO ORACLE (user="XXXXXX" password=XXXXXXXXXXXXXXXXXXX
30 PATH = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=xxxxx))
31 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME =xxxxxxxxx)))");
ERROR: ORACLE connection error: ORA-12541: TNS:no listener.

[SASKiwi · Accepted solution]

@QHO  - Agreed. Your DBA is the best person to figure this out. Looks like you are trying to define the Oracle connection completely in SAS. This is good as that means you don't have to tinker with your TNSNAMES.ORA file.

[acordes]

Exactly, in my company business users are familiar with queries in a BI application through a drag&drop approach. They can copy the oracle query code that runs in the backgroups and plug it in in the sas code like you decribed. 

It's a huge time saver and we have the data directly in sas building up on the users' expertise to make the query and put the filters they require.  

[Tom]

Figure it out.  Here is link to Oracle definition of the DECODE() function

Formatting the code will make it easier to see what it is trying to do.

 DECODE(
   DECODE(LC.ITEM_TYPE
         ,'L',NVL(LC.ITEM_EYE,'B')
         )
 ,'L',TOTAL_COST
 ) AS L_LENS_COST

Which looks a little bit silly.  It is re-coding (decode seems like a misnomer for this function) the L values in ITEM_TYPE to either ITEM_EYE or B.  Then it is re-coding L in the result (which is only possible if both ITEM_TYPE and ITEM_EYE is L) to TOTAL_COST.

So perhaps they meant:

case when (lc.item_eye = 'L' and lc.item_type='L') then total_cost end

[QHO]

Thanks Tom.  

Yes, it works.  Very helpful explanations!

Much appreciations.

Quinn

[Sajid01]

Hello 
As @SASKiwi has pointed out you can run on it Oracle using SAS SQL pass through.
However one must remember that SAS and Oracle are different applications each performing SQL processing and each has its own enhancements not available in the other.
Therefore a better approach is to determine what your code is doing in Oracle and then rewrite  ir ia SAS  to perform the same tasks.
Have a good book like Mastering Oracle SQL (Sanjay Mishra et al) or some similar handy to understand the Oracle SQL Syntax.

 

[SASKiwi]

@Sajid01 - While I generally agree with your approach, sometimes SQL Passthru and Oracle-specific SQL is best. I've often found you have to use Oracle hints to get reasonable query performance for example. 

[Sajid01]

Thanks @SASKiwi