Executing a sql stored proc through sas and getting the results in to sas

[Swathi12]

Hi,

 I tried to execute a sql stored proc and it went well.

But how to get the results to sas log.

The sas program is :

PROC SQL;

CONNECT TO ODBC AS ods ( DATAsrc=PriorityYou_Test USER='XXXXXXXXXX' PASSWORD='XXXXXX');
execute ([PriorityYou].[dbo].[HelloWorld] ) by ods;
%PUT &SQLXRC. &SQLXMSG. ;
DISCONNECT FROM ods;
QUIT;

Do we need to add any options or is there any other way?

[SASKiwi]

What results are you referring to? If the stored procedure produces a result set, this cannot be read by the EXECUTE statement. The way around this is to create a temporary table or view in the EXECUTE step, then follow that with a SELECT from CONNECTION TO ODS statement.

[ScottBass]

I do this all the time (MS SQL Server), here is example code:

proc sql;
   connect using rldx;
   create table rldx_operational_metrics as
   select * 
   from connection to rldx (
EXEC etl.pLoad_RLDX_OPERATIONAL
   );
quit;

In this example, the stored procedure loads a target table, then returns metrics about the load as a result set.  I want to display those results in EG, so I create a table, and later use PROC PRINT to display formatted results.

Read the doc for options that may be unclear, such as connect using <ODBC libref>.

P.S.: Probably not a great idea to post your database, user name, and password in a public forum.  Your firewall is probably secure, but anyone inside Westpac that has access to your server, and reads this post, could probably read your data.  Up to you whether that complies with your internal security requirements.  If not, perhaps edit your post 😉

[Swathi12]

Thanks a lot for your guidance.

But the output of my SQL Sp is below:

proc [HelloWorld] had started at : 02/06/19  3:24:42 PM

Hello World from TEST ODS

Line 1

Line 2

Stored proc [HelloWorld] had ended at : 02/06/19  3:24:42 PM

where as i am getting below output from the dataset 

Obs x

below is the sas code.

PROC SQL;

CONNECT TO ODBC AS ods ( DATAsrc=PriorityYou_Test USER='******' PASSWORD='******');
create table one as select * from connection to ods(
execute ([PriorityYou].[dbo].[HelloWorld] ) by ods;

DISCONNECT FROM ods;
QUIT;

proc print data=one;
run;

I want the entire thing done by thr Sp.

Is it possible in SAS?

First of all running a SQL SP through SAS and getting the whole output in sas possible?

[ScottBass]

Create this stored procedure in SQL Server:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE dbo.fubar
	-- Add the parameters for the stored procedure here
	@switch INT=1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF @switch = 1
	BEGIN
		SELECT * FROM sys.databases
	END
	ELSE
	IF @switch =2 
	BEGIN
		DECLARE @MyTable TABLE (line VARCHAR(50))
		INSERT INTO @MyTable
		VALUES ('FOO'),('BAR'),('BLAH')
		SELECT * FROM @MyTable
	END
END
GO

Run this code in SAS:

%libname_sqlsvr(libref=MyLib,server=<your server>,database=<your database>,schema=dbo)

proc sql;
   connect using mylib;
   create table fubar as
   select * from connection to mylib(
EXEC dbo.fubar
   )
quit;
proc print;
run;

proc sql;
   connect using mylib;
   create table fubar as
   select * from connection to mylib(
EXEC dbo.fubar @switch=1
   );
quit;
proc print;
run;

proc sql;
   connect using mylib;
   create table fubar as
   select * from connection to mylib(
EXEC dbo.fubar @switch=2
   );
quit;
proc print;
run;

https://github.com/scottbass/SAS/blob/master/Macro/libname_sqlsvr.sas

If you still have problems, 1) hit the SAS and/or SQL Server doc as needed, 2) ask a colleague, or 3) repeat #1.

Hope this helps...

P.S.:  My guess is you're expecting SAS to return PRINT statements.  It doesn't work that way.  If that's the case, see #1.

Lastly, if you return multiple result sets, SAS will only pick up the first one (using the above technique).  You have to combine all your result sets somehow (if you really need to do this). 

Otherwise, if you REALLY need multiple, separate results, your stored procedure could create multiple tables, either in a tmp schema in your database, or in ##global temp tables, then you could print them with multiple print procedures, then delete the tables from SAS.  Cumbersome but would work.