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).
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).
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.