I amn't able to populate the date field values into SAS datasets when I tried to populate the dataset as a resultset of a DB2 stored procedure.
CREATE PROCEDURE sas.SASSP0003(in id_start decimal(10), in id_end decimal(10))
DYNAMIC RESULT SETS 1
DECLARE C1 CURSOR with return FOR SELECT START_DT from sas.srctbl where id between id_start and id_end;
connect to db2(user=userName password=Password database=sasdev);
create table UsrLib.TEMPSASTBL as select * from connection to db2
(call sas.SASSP0003(100001,100020)) ;
Can anyone help me to get the date values accordingly?
I assume that you have tested you stored procedure outside SAS already.
Does the call work but your SAS table will be empty? What are the values of SQLXRC and SQLXMSG macro variables? Any usable hints from the SAS LOG?
I am able to invoke the above procedure at db2 end using the same call statement that I used in PROC SQL successfully and able to see the date values accordingly.
Here I am giving you the log details as well.
NOTE: The data value for column START_DT (START_DT) was truncated or was out of range 4 times when retrieving that data from the
NOTE: Table USRLIB.TEMPSASTBL created, with 21 rows and 1 columns.
25 %put SQLERRORCODE is &SQLXRC SQLERRORMesasage is &SQLXMSG ;
SQLERRORCODE is 0 SQLERRORMesasage is
I haven't used stored procedures in DB2, so I'm on thin ice here...
In DB2, dates can be represented between 0000-01-01 and 9999-12-31. In SAS, dates can range from 1582 and 20,000. My guess is that the DB2 stored procedure leave the data as numeric value (number of days after 0000-01-01), and SAS tries to interpret as number of days after 1960-01-01.
Looking at your LOG, it seems that 17 rows were not truncated. If looking in SAS result table, are you finding the expected values there or not?