BookmarkSubscribeRSS Feed
FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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

 

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10
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?
Reeza
Super User

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. 

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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

Reeza
Super User

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

 

The + is the equivalemt of a LEFT JOIN. 

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4775 views
  • 2 likes
  • 3 in conversation