Date values are not populated into SAS result set from DB2 stored procedure

[deleted_user]

Hi,

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.

DB2 Procedure

CREATE PROCEDURE sas.SASSP0003(in id_start decimal(10), in id_end decimal(10))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR with return FOR SELECT START_DT from sas.srctbl where id between id_start and id_end;
OPEN C1;
END

PROC SQL

PROC SQL;
connect to db2(user=userName password=Password database=sasdev);
create table UsrLib.TEMPSASTBL as select * from connection to db2
(call sas.SASSP0003(100001,100020)) ;
QUIT;

Can anyone help me to get the date values accordingly?

Thanks in advance.

[LinusH]

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?

Regards,
Linus

[deleted_user]

Thanks for showing interest to help me

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
DBMS.
NOTE: Table USRLIB.TEMPSASTBL created, with 21 rows and 1 columns.

25 %put SQLERRORCODE is &SQLXRC SQLERRORMesasage is &SQLXMSG ;
SQLERRORCODE is 0 SQLERRORMesasage is
26 QUIT;


Kindly revert,

Thanks and Regards,
AnjiReddy.

[LinusH]

This note goes for date values, but I think it's aplicable for datetime values as well. What kind of datetimes do you have in your DB2 table?

http://support.sas.com/kb/2/429.html

/Linus

[deleted_user]

Thanks for your promptive response..

The date column is defined as date datatype only. You can find the column definition

START_DT SYSIBM DATE 4 0 Yes

In DB2 editor, by default date values are displayed in the layout MM/DD/YYYY only.

However I imported the table TEMPSASTBL into SAS repository and able to view the data as SAS displays the data in the layout DDMONYYYY

Thanks once again Linus for your time.

[LinusH]

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?

/Linus

[deleted_user]

The remaning 17 rows were having null values.
Me too think that SAS internally tries to convert the date values into numerics

[LinusH]

I don't think I can help you any further. I suggest that you open a track on SAS support.

/Linus