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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.