Hi, I have 2 tables (whole_set & mismatches) derived from 2 data steps (abc_vers & xyz_vers), as below: title ' full set '; create table whole_set as select source, rec_no, rec_01 from ( select 'abc', monotonic() as rec_no, rec_01 from abc_vers union select 'xyz', monotonic() as rec_no, rec_01 from xyz_vers ) as tbl01 (source, rec_no, rec_01) where source = 'abc' group by rec_01 having count(*) <= 2 order by rec_no ; title ' mismatch set '; create table mismatches as select * from ( select 'rowz2add', rec_01 from fis_vers except select 'rowz2add', rec_01 from nab_vers union select 'rowz2del', rec_01 from nab_vers where substr(rec_01,66,1) = 'L' and substr(rec_01,67,1) in ('0','1','2','3','4','5','6','7','8','9') and substr(rec_01,68,1) in ('0','1','2','3','4','5','6','7','8','9') and substr(rec_01,69,1) in ('0','1','2','3','4','5','6','7','8','9') except select 'rowz2del', rec_01 from fis_vers where substr(rec_01,66,1) = 'L' and substr(rec_01,67,1) in ('0','1','2','3','4','5','6','7','8','9') and substr(rec_01,68,1) in ('0','1','2','3','4','5','6','7','8','9') and substr(rec_01,69,1) in ('0','1','2','3','4','5','6','7','8','9') ) as tbl01 ( action, rec_01 ); My requirement - for every record in the "mismatches" table, its equivalent records to be deleted from the "whole_set" table... Used the "Delete" query below and it works when the 'Sub-Query" fetches 1 record but flops when there are multiple records: delete from whole_set where rec_01 = (select rec_01 from mismatches where action = 'rowz2del') ; Is there a way to iterate (in PROC SQL) through the rows and delete them from the whole_set (something like - get the count from mismatches table and then a 'DO' loop until all the records are deleted) ? Thanks, Mohan
... View more
