Create a data set table from a stored procedure temporary table

Question

I have a series of stored procedures all of which output a result set along with say 3/4 temporary tables created in each stored procedure.

What I would like to do is create an SAS data set table using the data in one of the temporary tables. At the moment, all I am managing to reference is the result set of the sp.

Code at the moment which gives me the sp result set:

DATA table_name
SQL "EXEC sp_name '" & %parameter1 & "', '" & %parameter2 & "','" & %parameter3 & "'" ;
RUN;

So, instead of referencing the result set as in the example, can I reference a temporary table?

Thanks!

Answer 1

You can achieve this with a SQL passthrough statement:

proc sql;
    connect to odbc as YourAlias ( dsn = YourDSNName connection = SHARED );
    execute (
            exec uspYourStoredProcedure;
            ) by YourAlias;

create table SAS.YourDataSet as 
    select * 
    from connection to YourAlias (
                                  select * 
                                  from #TempTableCreatedAbove
                                  );
disconnect from YourAlias;
quit;

You will need to modify the example as follows:

YourAlias = Give the connection an alias

YourDSNName = Your system DSN for the connection

uspYourStoredProcedure = Replace with the name of your stored procedure

YourDataSet = Provide a name for your new SAS Data Set

#TempTableCreatedAbove = Replace with the name of the temp table created as a result of executing your stored procedure

You should see a new SAS data set once you replace the values above and execute the statement.