Proc sql to underlying SQL code conversion

0
votes

I am interested in knowing if there is an existing tool / technique to convert proc sql statements into actual SQL / plSQL commands (that are sent to SQL server).

For example SAS code:

proc sql ; select datepart(date_time_column) as date format date9. from table; quit;

to PLSQL code:

SELECT TO_CHAR(date_time_column, 'DDMONYYYY') DATE FROM TABLE;

Background: I have a bunch of Proc SQL code just to fetch the data from source tables, and I need to automate it by creating a schema that matches our current workflow. The SAS specific functions such as "format date9." or "datepart" are many of the culprits that are slowing down the process of code conversion. So I am looking for better solutions that can provide me the actual PlSQL code that is being sent to the server using currently working SAS code.

All the pointers and suggestions will be greatly appreciated.

1
plsqlsastype-conversionproc-sql
You can use the SASTRACE option to see what SQL code is sent to the RDBMS, is that what you want? support.sas.com/resources/papers/proceedings15/3269-2015.pdf – Quentin
I honestly don't think there is a straight of the bat reference / method to do this. I have personally struggled with this also. The differences between ANSI SQL and PROC SQL are documented here: support.sas.com/documentation/cdl/en/proc/61895/HTML/default/…, I am not an Oracle person - so if there are standard ANSI to PL/SQL converters, then maybe you could standardize your PROC SQL queries to ANSI based on the above and then apply the converters. But still there will always be the SAS functions you have to manually deal with. – SAS2Python
Hi @Quentin, it is indeed a good way of doing it. I am exploring how much it eases my task. I will update you soon – Mandar

1 Answers

0
votes

Use the SASTRACE option to see what SQL code is sent to the RDBMS:

options sastrace=',,,d' sastraceloc=file 'c:\work\trace.log';

SASTRACE: Your Key to RDBMS Empowerment by Andrew Howell