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 -
With SAS -
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?
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).
Did you create a SAS data set? If so please show the code in addition to the libname statement.
And does your code have the word RETAIN in it anywhere? If so, that's likely your issue.
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
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...
@chimanbj Please post your code.
Also, I would consider contacting SAS Tech Support regarding this.
Here is the code:
libname dblib informix server=dbname;
proc print data = dblib.data_table(obs=10110 firstobs=10100);
format _all_;
run;
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?
Also experiencing this issue on a Linux box. Tested using explicit and pass-through SQL with no difference in results.
Yes, they're on it as we speak.
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.