topic Re: Convert PLSql components into a SAS program in SAS Programming

Convert PLSql components into a SAS program

Banu — Sat, 27 May 2017 10:27:00 GMT

Hi,
I have two quires in Oracle. 1st one is a main query. It contains joins on multiple tables and in this one of the table column value is passing into the Oracle function and it returns value to the main query.

Please suggest me how can write passing value into a Oracle function and returning values into a main query in SAS. Thanks InAdvance

Re: Convert PLSql components into a SAS program

LinusH — Sat, 27 May 2017 12:01:13 GMT

Please shaee an example on how you this today, as simplified as possible.
Also, would you like to populate the paramter?

Re: Convert PLSql components into a SAS program

Reeza — Sat, 27 May 2017 14:57:18 GMT

Search SQL Pass Thru

Re: Convert PLSql components into a SAS program

Banu — Sat, 27 May 2017 18:09:08 GMT

I have join as below.

QUERY:

select a.country_name,

b.state_name,

c.city_name,

d.product_type,

call_func(d.product_id) as product_name

from country a,state b,city c,product_info d

where a.country_id=b.country_id and

b.state_id=c.state_id and

c.product_type_id=d.product_type_id;

In the above quey tables are taken from different users.

FUNCTION:

CREATE or REPLACE FUNCTION call_func(product_id IN NUMBER) 
   RETURN VARCHAR2 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT a.product_name as produc_name 
      INTO product_name 
      FROM products a,categ b,price c
      WHERE a.product_id = product_id
            and a.categ_id=b.categ_id
            and b.price_id=c.price_id;
      RETURN(product_name); 
    END;

Please ignore in case of any syntax errors but query pupose is same.

Please let me know how can I do in SAS.

As of now, we copied required database tables in SAS and then doing SAS joins in proc sql.

So please suggest me in which ways how can i achieve above one.

Thanks In Advance.

Re: Convert PLSql components into a SAS program

LinusH — Sat, 27 May 2017 21:34:00 GMT

As @Reeza says, you need to use explicit SQL pass thru from SAS. ASFAIK SAS implicit pass thru cannot call UDF's.
If you wish to SAS SQL skip calling the function. To me it looks like it could be solved by using the products table in a join instead. Probably my ignorance of PLSQL, but where does b.categ_id and price_id come from?

Re: Convert PLSql components into a SAS program

SASKiwi — Sun, 28 May 2017 00:08:25 GMT

This post has a clear explanation of how to run unmodified Oracle SQL in SAS:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Urgent-help-in-converstion-of-oracle-to-AS/m-p/358926#M23625

Re: Convert PLSql components into a SAS program

Banu — Sun, 28 May 2017 01:55:47 GMT

Hi Linush, thanks for your reply. If input tables exist in different databases and those tables are involved in joining condition.inthese case how can I use explicit SQL pass through.Thanks.

Re: Convert PLSql components into a SAS program

Reeza — Sun, 28 May 2017 02:06:23 GMT

The same way you did before. It happens on the server, so if the server could run the code before it still can now.

If your data structure has changed that's a different story.

Re: Convert PLSql components into a SAS program

Banu — Sun, 28 May 2017 02:33:50 GMT

Thanks Reza, Just want to confirm you one thing, Can I include Oracle function query in place of call_func(d.product_id) as product_name. I hope in Oracle we can perform select clause in place of column name in Select statement. Please advice.

Re: Convert PLSql components into a SAS program

Reeza — Sun, 28 May 2017 03:24:30 GMT

If it works on the server it will work in pass through. Either way, test it out, very easy to test.

Re: Convert PLSql components into a SAS program

Banu — Mon, 29 May 2017 11:05:45 GMT

Hi Linush,

if I have only one PL/SQL procedure or function then I can call as mentioned below.

proc sql;

connect to oracle (user="oracleUserName" password="pass" path="serverPath" CONNECTION=GLOBAL CONNECTION_GROUP = SASAML);
execute( execute MyPackage.MyProcedure(&param1, &param2) ) by oracle;
disconnect from oracle;

quit;

If you see my query, Scenario is different.

For example, I have 10 variables in select clause and one of the variable value is passing to Oracle function and then doing

some business logic overe there and function returns back value into main query and rest of 9 columns are populated directly from other 9 tables by joining the tables.

Ex: select a,b,c,call_func(d) e,f,g from s1,s2......etc..

Please suggest me how can i achieve this in sas.

Re: Convert PLSql components into a SAS program

Tom — Mon, 29 May 2017 16:10:46 GMT

Don't use EXECUTE just use SELECT ... FROM CONNECTION TO ...

select * from connection to oracle
(/* Place ORACLE code inside the () */
select ......
);

Re: Convert PLSql components into a SAS program

LinusH — Mon, 29 May 2017 16:37:29 GMT

You got the answer from @Tom.
The from connection to construct is also considered as explicit SQL pass through, and is used when you want to return something to the SAS session.

Re: Convert PLSql components into a SAS program

Patrick — Mon, 29 May 2017 23:07:21 GMT

@Banu

With pass-through SQL you can run anything that works directly in Oracle (i.e. using SQL Developer) out of SAS.

SAS sends the code in the pass-through block "as-is" to Oracle for exection. So if your code works out of SQL Developer then it will work out of a pass-through block in SAS.

If you want SAS to execute something in Oracle without returning a result then wrap the Oracle code into an EXECUTE() block, else if you want a result set back then use SELECT * from connection to <oracle> ( <your Oracle SQL> );