DATA Step, Macro, Functions and more

Modify Statement - How do I update with Null Values?

Reply
N/A
Posts: 0

Modify Statement - How do I update with Null Values?

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.
Frequent Contributor
Posts: 139

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

Posted in reply to deleted_user
use the option UPDATEMODE=NOMISSINGCHECK
in the modify statment line;

Regards
Darryl
N/A
Posts: 0

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

Posted in reply to darrylovia
Excellent! Worked like a charm. Thanks! Don't know why that option wasn't mentioned anywhere in the online documentation for the Modify statement.
Super Contributor
Super Contributor
Posts: 3,174

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

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

Scott Barry
SBBWorks, Inc.
Ask a Question
Discussion stats
  • 3 replies
  • 189 views
  • 0 likes
  • 3 in conversation