How does the SAS Modify statement work?

[PoornimaRavishankar]

Hi,

I wrote this code -

data one;
a=1;
b=2;
c=3;
d=4;
run;
proc sort; by a;
proc print;
proc contents;

data two;
a=1;
b=5;
c=6;
output;
a=2;
b=9;
c=10;
output;
run;
proc sort; by a;
proc print;
proc contents;

data one;
modify one two;
by a;
select (_IORC_);
when (%SYSRC(_SOK)) do;
replace;
end;
when (%SYSRC(_DSENMR)) do;
output;
_error_=0;
end;
when (%SYSRC(_DSEMTR)) do;
put 'ERR' 'OR: Duplicate Values on transaction dataset';
stop;
end;
otherwise do;
end;
end;
run;

proc print;
proc contents;

When I ran it, I found (much to my horror) that the last step drops the variable d from dataset one. (modify_horror.txt)

Is this how SAS modify is supposed to work. I know explicit keep and drop statements will not work in a MODIFY step? How is this possible then?

Note: When I add the variable d to dataset, it appears in the final dataset. (modify_better.txt) But it shows values for d when I would expect it to show missing (i.e. for a = 2).

[ballardw]

Perhaps you are are looking for the UPDATE statement instead of MODIFY. Replace is used to replace the entire observation, not selected variables. UPDATE replaces values of selected varaibles (and adds observations if there are values of the by variable in the update data set not on the master).

[rogerjdeangelis]

This might help you understand modify but as the previous responder stated for columh updates you may want to look at update.

Stackoverflow SAS: Modify master table with transation table while no obs in master table SAS

Data _Nulls_ correct interpretaion of the problem:

I need MODIFY a SAS dataset and tell SAS to REPLACE or OUTPUT
depending on if the records are matched or not.

Data_null_s excellent solution deserves a closer look

HAVE ( BASE and UPDATEX datasets)

http://goo.gl/TqYBNm

data base;
    input Field1 $ Field2 $ Field3 $ ;
    datalines;
 0001 501 F
 0001 502 NF
 0002 601 NF
 0002 602 NF
    ;
run;

data updateX;
    input Field1 $ Field2 $ Field3 $ ;
    datalines;
0001 502 F
0002 602 F
0003 603 F
    ;
run;

My desired output

0001 501 F
0001 502 F
0001 502 NF
0002 602 F
0003 603 F


WPS/SAS DATA_NULL_ SOLUTIION

Let me try to explain what data_null_ did. Hope it is correct?

data base;
   modify base updatex;
   by field1 field2;
   /* only three records are processed
     _IORC_=0 0001 502 F
     _IORC_=0 0002 602 F
     _IORC_=1230013 0003 603 F
   */
   put _iorc_= field1-field3 ;
   if _iorc_ eq 0 then replace;
   else do;
      output;
      _error_=0;
      end;
   run;


Whats going on

0001 501 F       Base record is not even read but remains in base
0001 502 F       Match(_IORC_=0) update NF to F
0001 502 NF      No additional update record is available. Record remains in base.
0002 602 F       Match(_IORC_=0) update change NF to F
0003 603 F       NoMatch(_IORC_=1230013) Record only exists in update so it will be added

* WPS solution:

It is worth noting that WPS also gave the same result, however only
when using WPS datasets. I go this note when I tried to use a SAS dataset

Note: SAS7BDAT datasets are not supported yet.

%let wrk=%sysfunc(pathname(work));
%utl_submit_wps64(
libname wrk '&wrk';

/* convert to WPS datasets */
data base;set wrk.base;run;quit;
data updatex;set wrk.updatex;run;quit;

data base;
   modify base updatex;
   by field1 field2;
   /* only three records are processed
     _IORC_=0 0001 502 F
     _IORC_=0 0002 602 F
     _IORC_=1230013 0003 603 F
   */
   put _iorc_= field1-field3 ;
   if _iorc_ eq 0 then replace;
   else do;
      output;
      _error_=0;
      end;
   run;
);


DATA_NULL_ POSTSCRIPT:

It is easier if you can create a new data set using UPDATE.
With update the matching records are updated and then output
(replaced) and new records from the transaction file are output.

data ubase;
   update base updatex;
   by field2 field3;
   run;

WPS LOG

The WPS System     06:33 Saturday, August  6, 2016

NOTE: (c) Copyright World Programming Limited 2002-2016.  All rights reserved.
NOTE: World Programming System 3.02 (03.02.02.00.015680)
      Licensed to CompuCraft, for express only
NOTE: This session is executing on the X64_WIN7PRO platform and is running in 64 bit mode

NOTE: This session is executing in WPS EXPRESS edition mode and is limited to processing only
      100 records from any input dataset or file.

NOTE: AUTOEXEC processing beginning; file is c:\oto\Tut_Otowps.sas
NOTE: Format num2mis output
NOTE: Format $chr2mis output
NOTE: Procedure format step took :
      real time : 0.015
      cpu time  : 0.015



NOTE: The data step took :
      real time : 0.000
      cpu time  : 0.000


NOTE: AUTOEXEC processing completed

1         libname wrk 'd:\wrk\_TD3760_BACKUP-PC_';
NOTE: Library wrk assigned as follows:
      Engine:        SAS7BDAT
      Physical Name: d:\wrk\_TD3760_BACKUP-PC_

2         data base;
3         set wrk.base;
4         run;

NOTE: 4 observations were read from "WRK.base"
NOTE: Data set "WORK.base" has 4 observation(s) and 3 variable(s)
NOTE: The data step took :
      real time : 0.015
      cpu time  : 0.015


5         quit;
6         data updatex;
7         set wrk.updatex;
8         run;

NOTE: 3 observations were read from "WRK.updatex"
NOTE: Data set "WORK.updatex" has 3 observation(s) and 3 variable(s)
NOTE: The data step took :
      real time : 0.000
      cpu time  : 0.000


9         quit;
10        data base;
11        modify base updatex;
12        by field1 field2;
13        put _iorc_= field1-field3;
14        if _iorc_ eq 0 then replace;
15        else do;
16        output;
17        _error_=0;
18        end;
19        run;



2                                                                             The WPS System



_IORC_=0 0001 502 F
_IORC_=0 0002 602 F
_IORC_=1230013 0003 603 F
NOTE: Master dataset "WORK.base" was updated - 0 records were removed, 2 records were replaced and 1 records were added.
NOTE: 3 observations were read from transaction dataset "WORK.updatex".
NOTE: The data step took :
      real time : 0.015
      cpu time  : 0.000



NOTE: Submitted statements took :
      real time : 0.062
      cpu time  : 0.046

[PoornimaRavishankar]

Thank you, not sure what WPS is.  Is it a new product?

So my question is this, I know the modify statement is unable to modify what's already in the PDV. So how come it drops variables when not found in the transaction dataset? 

My expectation was that the step would leave those extra fields in the master alone, but apparently not.

Anyway, thank you for the response with examples. Very helpful.