BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chimanbj
Obsidian | Level 7

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
mattshev
Fluorite | Level 6

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

11 REPLIES 11
ballardw
Super User

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

Reeza
Super User

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

Tom
Super User Tom
Super User

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
chimanbj
Obsidian | Level 7

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

Reeza
Super User

@chimanbj Please post your code.

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

chimanbj
Obsidian | Level 7

Here is the code:

libname dblib informix server=dbname;

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

LinusH
Tourmaline | Level 20

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
mattshev
Fluorite | Level 6

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

Reeza
Super User
Have you contacted tech support? They should be in on this conversation.
mattshev
Fluorite | Level 6

Yes, they're on it as we speak.

mattshev
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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