DATA Step, Macro, Functions and more

SAS pulling data incorrectly from Informix

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

SAS pulling data incorrectly from Informix

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?

 


Accepted Solutions
Solution
‎03-30-2016 10:46 AM
Occasional Contributor
Posts: 5

Re: SAS pulling data incorrectly from Informix

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).

View solution in original post


All Replies
Super User
Posts: 11,343

Re: SAS pulling data incorrectly from Informix

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

Super User
Posts: 19,770

Re: SAS pulling data incorrectly from Informix

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

Super User
Super User
Posts: 7,039

Re: SAS pulling data incorrectly from Informix

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
Contributor
Posts: 25

Re: SAS pulling data incorrectly from Informix

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

Super User
Posts: 19,770

Re: SAS pulling data incorrectly from Informix

@chimanbj Please post your code.

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

Contributor
Posts: 25

Re: SAS pulling data incorrectly from Informix

Here is the code:

libname dblib informix server=dbname;

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

Super User
Posts: 5,424

Re: SAS pulling data incorrectly from Informix

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?

Data never sleeps
Occasional Contributor
Posts: 5

Re: SAS pulling data incorrectly from Informix

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

Super User
Posts: 19,770

Re: SAS pulling data incorrectly from Informix

Have you contacted tech support? They should be in on this conversation.
Occasional Contributor
Posts: 5

Re: SAS pulling data incorrectly from Informix

Yes, they're on it as we speak.

Solution
‎03-30-2016 10:46 AM
Occasional Contributor
Posts: 5

Re: SAS pulling data incorrectly from Informix

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).

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 451 views
  • 4 likes
  • 6 in conversation