SQL pass-through SAS/ SAS EG - return a table

Question

Case: I want to run a SQL pass-through from SAS and I want an output in my work libary.

Problem: The log tells me everything is fine but there is no dataset output? I am a big rookie on this area - please help.

Proc sql;

connect to odbc as mydb
    (dsn=x user=x password=x);


execute (  
DECLARE @return_value int 
EXEC    fpt.usp_Marcus_Buy_and_Sell_amounts
@Country ='DK',
@Date ='2016-01-01', 
@Date2='2016-02-01'  

SELECT  'Return Value' = @return_value 







        ) by mydb;


Quit;

Jetzler · Accepted Answer · 2016-03-10T07:50:42.423

1

So far you are only asking SAS to execute a query in the Database. To get a table back to your SAS work library you will need to include a create table statement on the SAS side.

e.g.

Proc sql;

connect to odbc as mydb
    (dsn=x user=x password=x);

create table mydb_return as select * 
       from connection to mydb
         ( EXEC fpt.usp_Marcus_Buy_and_Sell_amounts
            @Country ='DK',
            @Date ='2016-01-01', 
            @Date2='2016-02-01' 
         );

disconnect from mydb;

Quit;

edit: changed statement according to the comments

edited Mar 10 '16 at 07:50

answered Mar 09 '16 at 08:22

Jetzler

787
3
11

When I run your code the log says: ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@return_value". : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. But I just declared the variable "@Return_value"? – Jannick Mikkelsen Mar 09 '16 at 10:01
1

Ok, variables are not global. Try directly inserting the `EXEC fpt.usp_Marcus_Buy_and_Sell_amounts @Country ='DK', @Date ='2016-01-01', @Date2='2016-02-01'` into the create table statement, without declare and select. – Jetzler Mar 09 '16 at 12:34

score 0 · Answer 2 · answered Mar 09 '16 at 14:02

Jetzler answer is great! Though I found out I could reduce the code to just :

Proc sql;

connect to odbc as mydb
    (dsn=x user=x password=x);

create table mydb_return as select * 
       from connection to mydb
         (EXEC    fpt.usp_Marcus_Buy_and_Sell_amounts
@Country ='DK',
@Date ='2016-01-01', 
@Date2='2016-02-01'  
 ) ;

disconnect from mydb;


Quit;

SQL pass-through SAS/ SAS EG - return a table

2 Answers2