topic Re: SAS pulling data incorrectly from Informix in SAS Programming

SAS pulling data incorrectly from Informix

chimanbj — Tue, 08 Mar 2016 19:53:01 GMT

Hello!

We are running SAS 9.4 (TS1M3), and have found a bizzare problem. When pulling data from an Informix database, we are finding that SAS is not re-initializing fields after every record. This is resulting in data from the previous record being put into the subsequent record, if the subsequent record is blank.

Here is an example, for clarity:

In Informix -

Record 1 -> Total Charge value of 5494.32
Record 2 -> Total Charge is missing

With SAS -

Record 1 -> Total Charge value of 5494.32
Record 2 -> Total Charge value of 5495.32

The SAS information was determined by using Proc Print. The connection is made with a libname statement (libname state informix server=dbname).

Has anyone seen this, and can tell me what might be causing this issue?

Re: SAS pulling data incorrectly from Informix

ballardw — Tue, 08 Mar 2016 20:04:44 GMT

Did you create a SAS data set? If so please show the code in addition to the libname statement.

Re: SAS pulling data incorrectly from Informix

Reeza — Tue, 08 Mar 2016 20:15:12 GMT

And does your code have the word RETAIN in it anywhere? If so, that's likely your issue.

Re: SAS pulling data incorrectly from Informix

Tom — Tue, 08 Mar 2016 20:34:40 GMT

Please post the SAS step that is reading the data. Perhaps you have accidentally codes a many-to-many merge?

For example here is code that merges 3 observations with 2 observations.

data one;
   input id arow avalue ;
cards;
1 1 10
1 2 20
1 3 30
;
data two ;
   input id brow bvalue;
cards;
1 1 5
1 2 15
;
data both;
  merge one two ;
  by id;
run;

Notice that in the resulting output the last record from TWO is repeated since there are no new records read to overwrite the values.

id    arow    avalue    brow    bvalue
 1      1       10        1        5
 1      2       20        2       15
 1      3       30        2       15

Re: SAS pulling data incorrectly from Informix

chimanbj — Tue, 08 Mar 2016 22:06:16 GMT

The issue is happening during a PROC PRINT. Others have reported the same issue with a Data Step. I wish it were as simple as a Data Step. I'm thinking it has something to do with the Informix driver, but I'm not sure...

Re: SAS pulling data incorrectly from Informix

Reeza — Tue, 08 Mar 2016 22:29:16 GMT

@chimanbj Please post your code.

Also, I would consider contacting SAS Tech Support regarding this.

Re: SAS pulling data incorrectly from Informix

chimanbj — Tue, 08 Mar 2016 22:45:19 GMT

Here is the code:

libname dblib informix server=dbname;

proc print data = dblib.data_table(obs=10110 firstobs=10100);
format _all_;
run;

Re: SAS pulling data incorrectly from Informix

LinusH — Wed, 09 Mar 2016 08:38:52 GMT

For debugging purposes, use:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

Also, try SQL and/or a data step.

And explicit SQL pass-thru.

Any differences in the result?

Re: SAS pulling data incorrectly from Informix

mattshev — Wed, 09 Mar 2016 14:43:12 GMT

Also experiencing this issue on a Linux box. Tested using explicit and pass-through SQL with no difference in results.

Re: SAS pulling data incorrectly from Informix

Reeza — Thu, 10 Mar 2016 03:13:21 GMT

Have you contacted tech support? They should be in on this conversation.

Re: SAS pulling data incorrectly from Informix

mattshev — Thu, 10 Mar 2016 14:06:59 GMT

Yes, they're on it as we speak.

Re: SAS pulling data incorrectly from Informix

mattshev — Fri, 11 Mar 2016 14:37:17 GMT

This was resolved by updating the Informix client software, which was at v3.5. It's unfortunate that no errors or warnings are issued if you have SAS v9.4 and don't update to 4.1 or higher.

From the Systems Requirements for SAS 9.4 Foundation for Linux for x64:

SAS/ACCESS Interface to Informix

For SAS 9.4M3

DBMS Product Required: Informix Client SDK 4.10FC4 (64-bit only).