BookmarkSubscribeRSS Feed
DNEnterprise
Fluorite | Level 6

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?

5 REPLIES 5
Panagiotis
SAS Employee

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
Super User
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
Fluorite | Level 6

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

Panagiotis
SAS Employee

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
Fluorite | Level 6

Very helpful.  Thanks

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 369 views
  • 3 likes
  • 3 in conversation