calling stored procedure in SAS DI

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

calling stored procedure in SAS DI

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.


Accepted Solutions
Solution
‎09-26-2013 10:37 PM
Super User
Posts: 3,115

Re: calling stored procedure in SAS DI

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;

View solution in original post


All Replies
Solution
‎09-26-2013 10:37 PM
Super User
Posts: 3,115

Re: calling stored procedure in SAS DI

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;

Contributor
Posts: 70

Re: calling stored procedure in SAS DI

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.

Contributor
Posts: 70

Re: calling stored procedure in SAS DI

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

Can anyone point me in the right direction?

Contributor
Posts: 52

Re: calling stored procedure in SAS DI

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 ?
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 4102 views
  • 0 likes
  • 3 in conversation