SAS _iorc_ delete (remove)

[scb]

Is there a way to delete record in 'master' table if the record is not available in 'transact' table? For below code, id 01 should be deleted as 01 is not available in transact table.

i tried this but not work.

if _iorc_ eq 1 then do;
_reason_change_ = 'Remove';
remove;
end;

/**program start**/

data master;
input id $ name $ gender $ weight;
datalines;
01 Perry M 165
02 Miller M 145
03 Davis F 127
;
run;

data transact;
input id $ name $ gender $ weight;
datalines;
02 Miller . 160
03 Bush . 157
05 Elliot F 125
02 . M 170
;
run;

proc sort data=master; BY id; RUN;
proc sort data=transact; BY id; RUN;

proc datasets lib=work;
audit master;
initiate;
user_var _reason_change_ $30;
quit;

data master;
modify master transact;
by id;
if _iorc_ eq 0 then do;
_reason_change_ = 'Update';
replace;
end;
else if _iorc_ eq 1230013 then do;
_reason_change_ = 'new';
output;
end;
else do;
errmsg=IORCMSG();
putlog 'NOTE: ' errmsg= _iorc_=;
end;
run;

data auditchk;
set Master(type=audit);
run;

[s_lassen]

No, not the way you do it. When you use 

modify master transact; 
by id

the records in the master dataset are read using dynamic WHERE clauses, and the records with IDs that are not in TRANSACT are never read from MASTER.

You can do it by adding an extra step, like this:

data master;
  modify master transact;
  by id;
  if _iorc_ eq 0 then do;
    _reason_change_ = 'Update';
    replace;
    end;
  else if _iorc_ eq 1230013 then do;
    _reason_change_ = 'new';
	_error_=0; 
    output;
    end;
  else do;
    errmsg=IORCMSG();
    putlog 'NOTE: ' errmsg= _iorc_=;
    end;
run;
proc sql;
  create index id on transact(id); 
quit; 

data master;
  modify master;
  set transact key=id;
  if _iorc_ then do;
    _reason_change_='Delete';
	_error_=0;
	remove;
	end;
run; 

But your _reason_change_ will not get written to the audit table, as it is written to the new record, not the old, and there is no new record when you remove.

[yabwon]

Hi,

If you can modify your master tabel a little bit maybe following code will do the job for you.

Bart

/**program start**/

data master;
input id $ name $ gender $ weight;
_A_=.; /* additiona variable which marks if a given 
          record from master apeared in transact
       */
datalines;
01 Perry M 165
02 Miller M 145
03 Davis F 127
;
run;

data transact;
input id $ name $ gender $ weight;
datalines;
02 Miller . 160
03 Bush . 157
05 Elliot F 125
02 . M 170
;
run;

proc sort data=master; BY id; RUN;
proc sort data=transact; BY id; RUN;

proc datasets lib=work;
audit master;
initiate;
user_var _reason_change_ $30;
quit;

/* before modyfication set marker's value */ 
%let marker=%sysfunc(datetime());

data master;
modify master transact;
by id;
put "*" _N_=;
if _iorc_ eq 0 then do;
_reason_change_ = 'Update';
_A_ = symget('marker'); /* refresh value of marker variable */
replace;
end;
else if _iorc_ eq 1230013 then do;
_reason_change_ = 'New';
_A_ = symget('marker'); /* refresh value of marker variable */
output;
end;
else do;
errmsg=IORCMSG();
_A_ = symget('marker'); /* refresh value of marker variable */
putlog 'NOTE: ' errmsg= _iorc_=;
end;
run;

data master;
modify master;
/* if value of marker variable is not equal to marker's value
   then given ID in master wasn't in transact
*/
if _A_ NE symget('marker') then do; _reason_change_ = 'Remove'; remove; end;
run;

data auditchk;
set Master(type=audit);
run;