topic Re: Running SQL stored procedure inside SAS in SAS Enterprise Guide

Running SQL stored procedure inside SAS

VSht — Tue, 11 May 2021 23:55:23 GMT

Hi,

Is it possible to run a SQL stored procedure inside SAS?

SQL developers want to have a SQL stored procedure, which can update a column in SQL table based on SAS table values.

We do not have ODBC, we connect SAS and SQL with pcfiles.

Thank you

Re: Running SQL stored procedure inside SAS

SASKiwi — Wed, 12 May 2021 00:43:59 GMT

Yes, using the EXECUTE statement in a PROC SQL passthru with an appropriate SAS/ACCESS product connecting to the database.

Here is an example.

No idea if this would work using SAS PC Files Server. Perhaps you could try it yourself.

Re: Running SQL stored procedure inside SAS

Sajid01 — Wed, 12 May 2021 00:50:23 GMT

The answer is no.
One needs to connect to the respective databases system which will execute the stored procedure.
SAS needs to connect to the databases system using SAS Access Interface to the respective data base system or atleast SAS Access Interface to ODBC, Then SAS passes the SQL code (SQL pass through) to the database to execute the code.
If you have SAS Access interface to PCFILES, then you can convert the data to csv or excel which can be used to upload the data to the databases.

Re: Running SQL stored procedure inside SAS

VSht — Wed, 12 May 2021 00:57:45 GMT

Thank you for your responses. Just one final comment because it seems not many people are aware that with Pcfiles you can create the same connection as with ODBC:

PROC SQL;
connect to pcfiles as myconn (server= port= dsn= user= password="");

create table SAS_table_name (compress = Y) as
select * from connection to myconn(
select *
from [SQL_table_name.] );
disconnect from myconn;
QUIT;

Re: Running SQL stored procedure inside SAS

SASKiwi — Wed, 12 May 2021 01:02:25 GMT

I'm aware of that approach. Can you try the EXECUTE statement and post your findings here. I'm interested to know!

Re: Running SQL stored procedure inside SAS

VSht — Wed, 12 May 2021 01:06:25 GMT

Thank you, I will try your suggestion.

However, I've just learned that this can be done easier - just by using a normal UPDATE statement and referring SQL Server LIBNAME.

Re: Running SQL stored procedure inside SAS

SASKiwi — Wed, 12 May 2021 01:08:09 GMT

proc sql;
   connect to pcfiles as myconn (server= port= dsn= user= password="")  ;
   execute (execute st_pr_name('parm')) by myconn;
   disconnect from myconn;
quit;

Re: Running SQL stored procedure inside SAS

VSht — Wed, 12 May 2021 01:10:09 GMT

Fantastic! Thank you so much!

Re: Running SQL stored procedure inside SAS

SASKiwi — Wed, 12 May 2021 01:10:24 GMT

If you have an SQL Server LIBNAME what engine does it use?

Re: Running SQL stored procedure inside SAS

VSht — Wed, 12 May 2021 01:18:59 GMT

We are using PC Files engine.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n1qk7lv0f535qkn1mydookwetyrp.htm

Re: Running SQL stored procedure inside SAS

Sajid01 — Wed, 12 May 2021 01:19:31 GMT

Thanks. I was not aware that SAS Access Interface to PC files can function as ODBC.
Wondering if it can connect to RDBMS for example Oracle on Unix systems?

Re: Running SQL stored procedure inside SAS

VSht — Wed, 12 May 2021 01:21:23 GMT

From my conversation to SAS TechSupport this morning I would guess - yes. But I have not tried.