topic Re: calling stored procedure in SAS DI in SAS Programming

calling stored procedure in SAS DI

jdmarshg — Fri, 27 Sep 2013 01:04:56 GMT

How does one go about calling a stored procedure in SQL Server... ?

I have tried many different variations of a proc sql statement.

I place this code within the precode of the SAS DI task.

I'm able to use the same proc sql statements to select from the database.

proc sql:

EXECUTE (exec libref.storeprocname);

quit;

Any suggestions and or guidance would be greatly appreciated.

Re: calling stored procedure in SAS DI

SASKiwi — Fri, 27 Sep 2013 02:37:58 GMT

Does your EXECUTE code work OK outside of SAS DIS just in a normal SAS program?

I would expect to see something like this. Your connection string may vary. This one is based on connecting via an ODBC driver using Windows Authentification.

Proc sql;

connect to MYSQL(noprompt = "server=MySQLServer;DRIVER=SQL Server;Trusted Connection=yes;DATABASE=MyDatabase;");

execute (

EXEC @return_value = MySchema.MyProcedure

) by MYSQL;

Quit;

Re: calling stored procedure in SAS DI

jdmarshg — Wed, 02 Oct 2013 17:24:00 GMT

Thank you so much that worked.

As I'm new to working with SAS and training is in the future.

I was trying to understand why a library would not work in this case and a pass through was needed but I believe I understand at this point.

Re: calling stored procedure in SAS DI

jdmarshg — Thu, 04 Sep 2014 20:02:51 GMT

How would I go about passing macro variables into the stored procedure?

Can anyone point me in the right direction?

Re: calling stored procedure in SAS DI

SAS_1001 — Thu, 22 Oct 2015 23:52:05 GMT

How do I capture the stored procedure values into SAS Macro Variable. I'd like to create a SAS Macro Variable and persist through the DI Job ?