08-10-2016 12:42 PM
I wrote this code -
proc sort; by a;
proc sort; by a;
modify one two;
when (%SYSRC(_SOK)) do;
when (%SYSRC(_DSENMR)) do;
when (%SYSRC(_DSEMTR)) do;
put 'ERR' 'OR: Duplicate Values on transaction dataset';
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).
08-10-2016 01:00 PM
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).
08-10-2016 08:59 PM
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
08-15-2016 04:15 PM
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.