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;
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