BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
3 REPLIES 3
darrylovia
Quartz | Level 8
use the option UPDATEMODE=NOMISSINGCHECK
in the modify statment line;

Regards
Darryl
deleted_user
Not applicable
Excellent! Worked like a charm. Thanks! Don't know why that option wasn't mentioned anywhere in the online documentation for the Modify statement.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Investigate using the UPDATEMODE=MISSINGCHECK|NOMISSINGCHECK parameter on the MODIFY statement to influence the missing-value handling behavior.

Scott Barry
SBBWorks, Inc.

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
  • 3 replies
  • 1096 views
  • 0 likes
  • 3 in conversation