topic Re: Modify Statement - How do I update with Null Values? in SAS Programming

Modify Statement - How do I update with Null Values?

deleted_user — Mon, 15 Sep 2008 14:20:11 GMT

I'm using a modify statement to update a master dataset with a transaction dataset, but I've found that null values in a field will not overwrite the corresponding non-null field. Is there an option or something to force null values to overwrite a non-null value when updating a record?

Here's the code I'm using:

data as400.ucl;
modify as400.ucl as400.ucl_update;
by claimno claimline;
if _iorc_=%sysrc(_sok) then replace;
else if _iorc_=%sysrc(_dsenmr) then do;
output; _error_=0; end;
else do; put "Unexpected error at Observation: " _n_; _error_=0; stop; end;
run;

So here's the situation: a record for a given claim number and claim line already exists in the master dataset, and the "Reject" field contains a value. I get an updated record in my update dataset (the transaction dataset) for the same claim number and claim line, but the "Reject" field is now null. When I try to update my master dataset, the Reject field containing a value will not seem to accept being changed to a null value. If I had a different value, then the field would be changed to that, but not to a null. The REPLACE statement is supposed to replace the observation in the master dataset with the observation from the transaction dataset, but this does not seem to be the case with null values. Please help.

Re: Modify Statement - How do I update with Null Values?

darrylovia — Mon, 15 Sep 2008 16:08:36 GMT

use the option UPDATEMODE=NOMISSINGCHECK
in the modify statment line;

Regards
Darryl

Re: Modify Statement - How do I update with Null Values?

sbb — Mon, 15 Sep 2008 16:08:55 GMT

Investigate using the UPDATEMODE=MISSINGCHECK|NOMISSINGCHECK parameter on the MODIFY statement to influence the missing-value handling behavior.

Scott Barry
SBBWorks, Inc.

Re: Modify Statement - How do I update with Null Values?

deleted_user — Tue, 16 Sep 2008 16:45:29 GMT

Excellent! Worked like a charm. Thanks! Don't know why that option wasn't mentioned anywhere in the online documentation for the Modify statement.