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.
This post has a clear explanation of how to run unmodified Oracle SQL in SAS:
Search SQL Pass Thru
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(¶m1, ¶m2) ) 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.
Don't use EXECUTE just use SELECT ... FROM CONNECTION TO ...
select * from connection to oracle
(/* Place ORACLE code inside the () */
select ......
);
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.
If it works on the server it will work in pass through. Either way, test it out, very easy to test.
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> );
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.