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.
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;
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;
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.
How would I go about passing macro variables into the stored procedure?
Can anyone point me in the right direction?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.