topic Re: How to convert Oracle SQL code to SAS code in SAS Programming

How to convert Oracle SQL code to SAS code

QHO — Sun, 15 May 2022 18:50:35 GMT

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.

Re: How to convert Oracle SQL code to SAS code

SASKiwi — Sun, 15 May 2022 20:45:05 GMT

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

Re: How to convert Oracle SQL code to SAS code

Tom — Mon, 16 May 2022 00:15:16 GMT

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

Re: How to convert Oracle SQL code to SAS code

QHO — Sun, 15 May 2022 22:36:37 GMT

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.

Re: How to convert Oracle SQL code to SAS code

QHO — Sun, 15 May 2022 22:39:33 GMT

Thanks Tom.

Yes, it works. Very helpful explanations!

Much appreciations.

Quinn

Re: How to convert Oracle SQL code to SAS code

Sajid01 — Sun, 15 May 2022 22:45:43 GMT

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.

Re: How to convert Oracle SQL code to SAS code

SASKiwi — Sun, 15 May 2022 22:54:22 GMT

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;

Re: How to convert Oracle SQL code to SAS code

SASKiwi — Sun, 15 May 2022 23:00:56 GMT

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

Re: How to convert Oracle SQL code to SAS code

Sajid01 — Mon, 16 May 2022 00:19:36 GMT

Thanks @SASKiwi

Re: How to convert Oracle SQL code to SAS code

QHO — Mon, 16 May 2022 00:27:01 GMT

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.

Re: How to convert Oracle SQL code to SAS code

SASKiwi — Mon, 16 May 2022 02:37:06 GMT

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

Re: How to convert Oracle SQL code to SAS code

acordes — Mon, 16 May 2022 10:53:59 GMT

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.