DATA Step, Macro, Functions and more

CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

Reply
Occasional Contributor FK1
Occasional Contributor
Posts: 15

CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

[ Edited ]

Hi Everybody,

 

I am confronted with the following problem:

 

I have an ORACLE-SQL written CODE in a "proc sql step" which is being forwarded by the means of the pass through facility to an oracle database which creates a table.

 

Now, I figured that due to performance issues it is better to create the table in SAS and NOT PASS IT THROUGH to the oracle database since all tables which are being needed for the creation of the table are also available in SAS-libraries. The question I have is:

 

Is there an "easy way" to convert the proprietary oracle-sql into ANSI (SAS) SQL ? I know that I do not have to use SQL in order to create the table but it is the way I have to do it. (don't ask :-)

 

Does anybody haven an idea how to convert the SQL-Code?

 

Kind regards,

FK1

 

Super User
Super User
Posts: 7,413

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

Yes, the easiest way to convert Oracle Specific SQL to SAS SQL is to open the code in your SAS editor, and go through the code line by line looking at what parts (specifically functions) are Oracle specifc, and what could be replaced with SAS functions.  There is no convert my code button.  As you have not provided any indication of the code I can't even give you example, but cast() is one that normally crops up.

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

Mhhh, too bad there is no "Convert my code button". I'll check, if I can provide the code. @RW9: are there any known issues with the "nvl" function?
Super User
Posts: 17,868

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

[ Edited ]

NVL() doesn't exist in SAS SQL

The equivalent is COALESCE/COALESCEC

 

I'd probably suggest just plugging in the SQL code, run, find the error, fix the error, return to step 2 and repeat until no error. If you have any WINDOW or PARTITION sections that logic will be need to be replaced. 

 

Edit: And once it doesn't generate errors compare it to the old results to ensure it's accurate. 

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

@Reeza: that's the point! Even if I have taken care of all the possible traps by replacing oracle sql functions with sas sql functions, there is no guarantee that the produced table is  exactly identical, is there?

 

I guess an alternative would be to use proc and data steps instead of sas sql. However, I end up having the same issues as with sas sql. There is no guarantee, I'll get the exact identiacl table as in the oracle-sql query..... Oh I wish there was a convert oracle-sql --> ANSI-SQL unicorn....... :-)

Super User
Super User
Posts: 7,413

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

There is no easy way to convert any language to any other, it always require your input.  As for being sure, just extract your data created on the database, then run the code you have, and proc compare the two.  That will highlight any differences.

Super User
Posts: 17,868

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL


FK1 wrote:

@Reeza: that's the point! Even if I have taken care of all the possible traps by replacing oracle sql functions with sas sql functions, there is no guarantee that the produced table is  exactly identical, is there?

 


Yes you can, use PROC COMPARE to compare the results from the different procs. You can make sure the logic is the same, unless your criteria was vague and inconsistent. 

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

Hi Folks,

 

here is the ORACLE-SQL CODE as promised:

In particular I am curious, if someone can tell me, how I "translate" the joins with (+) signs into ANSI-SQL.

So far I was able to replace the following oracle functions with equivalent SAS function:

 

decode() <--> CASE WHEN statement

nvl <--> coalescec function / coalesce function

lpad(to_char(TABLE2.COL2),9,'0')  <--> put(table2.COL2 , z9.)

 

 

	 SELECT
                  TABLE1.COL1,
                  TABLE2.COL1,
                  TABLE1.COL2,
                  TABLE1.COL3,
                  TABLE1.COL4,
                  decode(TABLE1.COL5,1,'A',2,'B',3,'C',4,'D',5,'E',6,'F',7,'G',8,'H',9,'I','NA'),
                  nvl(TABLE3_TOP.COL1, TABLE3.COL1),
                  TABLE4.COL1,
                  decode(TABLE1.COL6, 'STRING1', 'STRING1', 'STRING2', 'STRING2', 'OTHER'),
                  TABLE1.COL7,
                  TABLE1.COL8,
                  TABLE1.COL9,
                  TABLE1.COL10,
                  TABLE1.COL11,
                  TABLE1.COL12,
                  TABLE1.COL13,
                  TABLE5.COL1,
                  TABLE6.COL1,
                  decode(TABLE5.COL2,'A','STRING5','D','STRING6','NA'),
                  TABLE5.COL3+TABLE5.COL4,
                  TABLE6.COL2,
                  TABLE6.COL3,
                  TABLE6.COL4,
                  trim(TABLE72.COL1),
                  TABLE8_SRVR.COL1,
                  TABLE5.COL5,
                  TABLE5.COL6,
                  TABLE5.COL7,
                  TABLE9.COL1,
                  TABLE5.COL8,
                  TABLE5.COL9,
                  TABLE5.COL10,
                  sum((TABLE5.COL4 * TABLE5.COL6) + (TABLE5.COL3 * TABLE5.COL5)),
                 TABLE10.COL1,
                  nvl( TABLE112.COL1, 'NA' ),
                  TABLE5.COL11,
                  TABLE12.COL1
                FROM
                  TABLE1,
                  TABLE2,
                  TABLE3,
                  TABLE3  TABLE3_TOP,
                  TABLE4,
                  TABLE5,
                  TABLE6,
                  TABLE7  TABLE72,
                  TABLE8  TABLE8_SRVR,
                  TABLE9,
                  TABLE10,
                  TABLE11  TABLE112,
                  TABLE12,
                  ( 
                  select COL1, to_number (nvl (regexp_replace (COL2, '[^[:digit:]]', ''), 0)) as flag
                      from TABLE12
                      where COL2 is not null
                  )  DT_TABLE
                WHERE
                       ( TABLE1.COL1=TABLE2.COL2  )
                  AND  ( TABLE1.COL2=TABLE5.COL11  )
                  AND  ( TABLE12.COL1=TABLE1.COL2  )
                  AND  ( TABLE6.COL1=TABLE5.COL1  )
                  AND  ( TABLE72.COL2=TABLE6.COL5  )
                  AND  ( TABLE3.COL2(+)=TABLE1.COL1  )
                  AND  ( TABLE8_SRVR.COL2(+)=TABLE6.COL4  )
                  AND  ( trim(TABLE4.COL2(+)) = to_char(TABLE12.COL2)  )
                  AND  ( substr(TABLE10.COL2(+),2,9)=lpad(to_char(TABLE2.COL2),9,'0')  )
                  AND  ( TABLE1.COL14=TABLE3_TOP.COL2(+)  )
                  AND  ( TABLE9.COL2(+)=TABLE5.COL12  )
                  AND  ( TABLE10.COL1=TABLE112.COL2(+)  )
                  AND  ( DT_TABLE.COL1(+)=TABLE1.COL2  )
                  AND  
                  (
                   ( TABLE1.COL5 in (13,75,986) and TABLE1.COL1 not in ( '??','!!') and DT_TABLE.COL1 is null  )
                   AND
                   TABLE1.COL1  IN  
                     (
                     SELECT
                       TABLE1.COL1
                     FROM
                       TABLE1,
                       TABLE10,
                       TABLE2
                     WHERE
                       ( TABLE1.COL1=TABLE2.COL2  )
                       AND  ( substr(TABLE10.COL2(+),2,9)=lpad(to_char(TABLE2.COL2),9,'0')  )
                       AND  
                       TABLE10.COL1  IN  ( 'URZTAS'  )
                     )
                   AND
                   decode(TABLE5.COL2,'A','STRING5','D','STRING6','NA')  IN  ( 'STRING5'  )
                   AND
                   (
                    (
                     TABLE5.COL7  <>  0
                     AND
                     TABLE5.COL8  =  'Y'
                    )
                    OR
                     TABLE5.COL5  >  0
                   )
                   AND
                   trim(TABLE72.COL1)  <>  'TESTSTRING'
                   AND
                   (
                    TABLE8_SRVR.COL1  NOT IN  ( 'BLUBB1','BLUBB2','BLUBB3','BLUBB4','BLUBB5','BLUBB6','BLUBB7','BLUBB8' )
                    OR
                    TABLE8_SRVR.COL1  Is Null  
                   )
                  )
                GROUP BY
                  TABLE1.COL1, 
                  TABLE2.COL1, 
                  TABLE1.COL2, 
                  TABLE1.COL3, 
                  TABLE1.COL4, 
                  decode(TABLE1.COL5,1,'A',2,'B',3,'C',4,'D',5,'E',6,'F',7,'G',8,'H',9,'I','NA'), 
                  nvl(TABLE3_TOP.COL1, TABLE3.COL1), 
                  TABLE4.COL1, 
                  decode(TABLE1.COL6, 'STRING1', 'STRING1', 'STRING2', 'STRING2', 'OTHER'),
                  TABLE1.COL7, 
                  TABLE1.COL8, 
                  TABLE1.COL9, 
                  TABLE1.COL10, 
                  TABLE1.COL11, 
                  TABLE1.COL12, 
                  TABLE1.COL13, 
                  TABLE5.COL1, 
                  TABLE6.COL1, 
                  decode(TABLE5.COL2,'A','STRING5','D','STRING6','NA'),
                  TABLE5.COL3+TABLE5.COL4, 
                  TABLE6.COL2, 
                  TABLE6.COL3, 
                  TABLE6.COL4, 
                  trim(TABLE72.COL1), 
                  TABLE8_SRVR.COL1, 
                  TABLE5.COL5, 
                  TABLE5.COL6, 
                  TABLE5.COL7, 
                  TABLE9.COL1, 
                  TABLE5.COL8, 
                  TABLE5.COL9, 
                  TABLE5.COL10, 
                  TABLE10.COL1, 
                  nvl( TABLE112.COL1, 'NA' ), 
                  TABLE5.COL11, 
                  TABLE12.COL1

So, there are a couple of things remaining:

 

1.) How do I translate this into SAS:

(regexp_replace (COL2, '[^[:digit:]]', ''), 0)

 

2.)  How do I rewrite for example this join :

( TABLE8_SRVR.COL2(+)=TABLE6.COL4  )

 

Is this the same as saying

TABLE8_SRVR.COL2 left join TABLE6.COL4 ?

 

Any help is much appreciated.

 

FK1

Super User
Posts: 17,868

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

You also have a DECODE function that will need to be mapped. 

 

The + is the equivalemt of a LEFT JOIN. 

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: CONVERT ORACLE-SQL TO (SAS) ANSI-SQL

Just in case, someone want to know, here ist the "translation" of  (regexp_replace (COL2, '[^[:digit:]]', ''), 0)

into ANSI-SQL:

 

                  CASE
                    WHEN input(COL2, $1.) IN ("1" "2" "3" "4" "5" "6" "7" "8" "9")  THEN   1
                    ELSE 0
                 END    as flag

 

@ReezaI have already figured out the decode() function...

 

 

Ask a Question
Discussion stats
  • 9 replies
  • 322 views
  • 2 likes
  • 3 in conversation