Running SQL stored procedure inside SAS

[VSht]

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 

[SASKiwi · Accepted solution]

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. 

[Sajid01]

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.

[VSht]

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;

[SASKiwi]

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

[VSht]

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.

[SASKiwi]

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

[SASKiwi]

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

[VSht]

Fantastic! Thank you so much!

[VSht]

We are using PC Files engine.

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

[Sajid01]

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?

[VSht]

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