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.
... View more
