PROC SQL - Get count, iterate through a table that many times, delete records based on conditions

Mohan03 — Mon, 28 Dec 2020 07:09:38 GMT

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

Re: PROC SQL - Get count, iterate through a table that many times, delete records based on condition

Kurt_Bremser — Mon, 28 Dec 2020 08:02:25 GMT

Just use the IN operator:

where rec_01 in (select rec_01 from mismatches where action = 'rowz2del')

Re: PROC SQL - Get count, iterate through a table that many times, delete records based on condition

Mohan03 — Mon, 28 Dec 2020 08:26:04 GMT

It worked perfectly as expected.

Thank you @Kurt_Bremser for the sleek solution.

Thanks,

Mohan.

topic PROC SQL - Get count, iterate through a table that many times, delete records based on conditions in SAS Programming

PROC SQL - Get count, iterate through a table that many times, delete records based on conditions

Re: PROC SQL - Get count, iterate through a table that many times, delete records based on condition

Re: PROC SQL - Get count, iterate through a table that many times, delete records based on condition