I have years of stored procedures in SQL Server ; where the results are tried and true. Is it possible to call a stored procedure with parameters from SQL SERVER within the SAS enterprise, where I can run statistical analysis where the data set is based on the parameter?
Something like this?
proc sql;
connect to odbc (datasrc=development /*User=.... password=....*/);
create table test as
select *
from connection to odbc
(execute LastName @Last='Johnson'); /*Run this stored procedure*/
disconnect from odbc;
quit;
proc sql;
select mean(YearlyIncome) as Mean
from test;
quit;
My stored procedure finds all customers with a specific last name. I then create a table from the results of the stored procedure, and then can use it in SAS for analysis. I did a simple means in PROC SQL.
Here is another post that might help: https://communities.sas.com/t5/SAS-Enterprise-Guide/Execute-SQL-Stored-Procedure/td-p/411506
Thanks for the quick reply; I am definitely going to try to see if I can get this to work.
The paper @Reeza linked will give you more details. Also make sure you connection is set up already. My datasrc=Development option is specific to my connection to my SQL Server database. You should have one of your own.
- Peter
Very helpful. Thanks
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.