SQL SERVER within SAS Enterprise

[DNEnterprise]

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?

[Panagiotis]

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

[Reeza]

You can use SAS to call a stored procedure from SQL.

In fact, if you use something called SQL Pass through you can pass your commands directly to the server using the native SQL language rather than SAS.

https://www.lexjansen.com/wuss/2008/dmw/dmw09.pdf

[DNEnterprise]

Thanks for the quick reply; I am definitely going to try to see if I can get this to work.

[Panagiotis]

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

[DNEnterprise]

Very helpful.  Thanks