Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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


Accepted Solutions
Solution
‎08-17-2017 02:25 PM
New Contributor
Posts: 3

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

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

View solution in original post


All Replies
PROC Star
Posts: 1,167

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

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

New Contributor
Posts: 3

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

[ Edited ]

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.

 

 


sas code.PNGSAS Output 1.PNG
PROC Star
Posts: 1,167

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

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;

Super User
Super User
Posts: 7,039

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

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.

Solution
‎08-17-2017 02:25 PM
New Contributor
Posts: 3

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

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

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 351 views
  • 0 likes
  • 3 in conversation