The following message may be issued and/or you may experience missing data being returned when reading datetime data from an Informix database using the datetime format, DATETIME HOUR TO MINUTE.

NOTE: The data value for column xxxx(xxxx) was truncated or was out of range xxxxx times when retrieving that data from the DBMS.

SAS appears not to recognize this format. It returns the data as missing while other query products return the data properly. The DATETIME HOUR TO MINUTE type is really a TIME type, NOT a datetime type. However, Informix reports this column back as a datetime type. This is the reason you receive a missing value. To circumvent this problem, you can override the datatype with the SAS ACCESS dataset option, DBSASTYPE. For example:

proc print data=x.foo(dbsastype=(col1='time')); run;

However, missing data still may be returned using the DBSASTYPE circumvention if you are using PROC SQL to query the same Informix data with column name qualifiers. For example:

PROC SQL;
   CREATE TABLE SASUSER.QURY1134 AS
   SELECT A_INPAT_DISCHARGE.admit_time, A_INPAT_DISCHARGE.discharge_time
     FROM PHPDB5.A_INPAT_DISCHARGE(dbsastype=(admit_time='time'
            discharge_time='time'))
     WHERE (A_INPAT_DISCHARGE.inst_num = '1006' AND
             A_INPAT_DISCHARGE.fiscal_start_yr = 2002) ; QUIT;

However, using SELECT * does work fine and returns the data correctly. The following code will work:

PROC SQL;
   CREATE TABLE SASUSER.QURY1134 AS
     SELECT *
     FROM PHPDB5.A_INPAT_DISCHARGE(dbsastype=(admit_time='time'
            discharge_time='time'))
     WHERE (A_INPAT_DISCHARGE.inst_num = '1006' AND
            A_INPAT_DISCHARGE.fiscal_start_yr = 2002) ; QUIT;

It appears that this is a PROC SQL issue. The problem is the qualifier that is being used on the column names. As a workaround, you should be able to remove the "tablename." from the column name(s) in the SELECT list. Note: This was tested at SAS 9.1 and the issue has been corrected, i.e. you can specify column qualifiers and still have DBSASTYPE work.

Another work-around would be to to create a view in the database with derived columns. For example, here is the sample SQL for one of the derived columns. It uses the special TO_CHAR function.

(SUBSTR(to_char(a_inpat_discharge.admit_time),12,2 ))||(SUBSTR(to_char(a_inpat_discharge.admit_time),15,2))

The function TO_CHAR converts the datetime value (with hour to minute precision) to a full datetime value in character form (ccyy-mm-dd hh:mm:ss.00000). The SUBSTR function picks up the hh and mm portions of the string. Finally, the hh and mm strings are concatenated (||) together.