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
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.
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.
@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....... 🙂
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.
@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.
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
You also have a DECODE function that will need to be mapped.
The + is the equivalemt of a LEFT JOIN.
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.