topic Re: SQL SERVER within SAS Enterprise in SAS Procedures

SQL SERVER within SAS Enterprise

DNEnterprise — Thu, 29 Aug 2019 15:41:25 GMT

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?

Re: SQL SERVER within SAS Enterprise

Panagiotis — Thu, 29 Aug 2019 16:42:43 GMT

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

Re: SQL SERVER within SAS Enterprise

Reeza — Thu, 29 Aug 2019 16:43:14 GMT

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

Re: SQL SERVER within SAS Enterprise

DNEnterprise — Thu, 29 Aug 2019 17:12:41 GMT

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

Re: SQL SERVER within SAS Enterprise

Panagiotis — Thu, 29 Aug 2019 17:15:56 GMT

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

Re: SQL SERVER within SAS Enterprise

DNEnterprise — Thu, 29 Aug 2019 17:17:40 GMT

Very helpful. Thanks