topic Re: null values populated by last non null value while fetching data from informix in SAS Enterprise Guide

null values populated by last non null value while fetching data from informix

sbish7 — Sun, 23 Jul 2017 15:39:34 GMT

HI All,

We are facing very unusual issue while fetching data from informix database. All the null values are populated by last non null value. For ex. if the email for any customer is blank then while fetching it gets populated by the last non null email id. Is there any property that we need to set in sas management console for this.

Regards

Re: null values populated by last non null value while fetching data from informix

TomKari — Sun, 23 Jul 2017 18:04:32 GMT

I don't usually say this, but your post is so bizarre I'm wondering if something else is happening.

Can I suggest you create a query that demonstrates this behaviour with the simplest possible code, and then post that code?

I've never heard of anything even remotely resembling this before.

Tom

Re: null values populated by last non null value while fetching data from informix

sbish7 — Tue, 25 Jul 2017 19:17:05 GMT

please find attached the snapshots of the code and the output. this is the first time i have seen this issue. Could it be a driver issue.

Re: null values populated by last non null value while fetching data from informix

TomKari — Fri, 28 Jul 2017 21:02:33 GMT

I would contact SAS Tech Support on this one. Point them at exactly this conversation; you have provided an excellent example for them to see. I can't conceive of any explanation for this where the software is working correctly.

As a sanity check, I ran the following code and it returned exactly the expected results (second row email and mobile were null).

Tom

data Caddresses;

length serno 8 email $32 mobile 8;

serno = 165717; email = "md@gmail.com"; mobile =9523; output;

serno = 165718; call missing(email, mobile); output;

run;

proc sql;

select serno, email, mobile from Caddresses where serno in(165717, 165718);

quit;

Re: null values populated by last non null value while fetching data from informix

Tom — Fri, 28 Jul 2017 21:29:34 GMT

Check into what you are pulling from.

Perhaps it is a view and the WHERE clause is getting applied in an unexpected way.

Run it using explicit pass through mode.

Turn on logging options to see the exact SQL that SAS generate to perform the implicit pass thru.

Re: null values populated by last non null value while fetching data from informix

sbish7 — Thu, 17 Aug 2017 08:07:38 GMT

Issue is now resolved . We upgrade the informix odbc driver to version 4.10.fc9. now data is coming as expected.