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
Just use the IN operator:
where rec_01 in (select rec_01 from mismatches where action = 'rowz2del')
Just use the IN operator:
where rec_01 in (select rec_01 from mismatches where action = 'rowz2del')
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.