Deleting rows based on multiple columns conditions

Question

Given the following table have, I would like to delete the records that satisfy the conditions based on the to_delete table.

data have;
infile datalines delimiter="|";
input id :8. item :$8. datetime : datetime18.;
format datetime datetime18.;
datalines;
111|Basket|30SEP20:00:00:00
111|Basket|30SEP21:00:00:00
111|Basket|31DEC20:00:00:00
111|Backpack|31MAY22:00:00:00
222|Basket|31DEC20:00:00:00
222|Basket|30JUN20:00:00:00
;

+-----+----------+------------------+
| id  |   item   |     datetime     |
+-----+----------+------------------+
| 111 | Basket   | 30SEP20:00:00:00 |
| 111 | Basket   | 30SEP21:00:00:00 |
| 111 | Basket   | 31DEC20:00:00:00 |
| 111 | Backpack | 31MAY22:00:00:00 |
| 222 | Basket   | 31DEC20:00:00:00 |
| 222 | Basket   | 30JUN20:00:00:00 |
+-----+----------+------------------+

data to_delete;
infile datalines delimiter="|";
input id :8. item :$8. datetime : datetime18.;
format datetime datetime18.;
datalines;
111|Basket|30SEP20:00:00:00
111|Backpack|31MAY22:00:00:00
222|Basket|30JUN20:00:00:00
;

+-----+----------+------------------+
| id  |   item   |     datetime     |
+-----+----------+------------------+
| 111 | Basket   | 30SEP20:00:00:00 |
| 111 | Backpack | 31MAY22:00:00:00 |
| 222 | Basket   | 30JUN20:00:00:00 |
+-----+----------+------------------+

In the past, I used to operate with the catx() function to concatenate the conditions in a where statement, but I wonder if there is a better way of doing this

proc sql;
    delete from have
        where catx('|',id,item,datetime) in 
              (select catx('|',id,item,datetime) from to_delete);
run;

+-----+--------+------------------+
| id  |  item  |     datetime     |
+-----+--------+------------------+
| 111 | Basket | 30SEP21:00:00:00 |
| 111 | Basket | 31DEC20:00:00:00 |
| 222 | Basket | 31DEC20:00:00:00 |
+-----+--------+------------------+

Please note that it should allow the have table to have more columns than the table to_delete.

score 2 · Accepted Answer · answered May 17 '22 at 14:33

2

You can use except from to compute difference set of two sets:

proc sql;
  create table want as 
  select * from have except select * from to_delete
  ;
quit;

answered May 17 '22 at 14:33

whymath

1,262
9
16

What if the have table has more columns that to_delete? – Kermit May 17 '22 at 16:22
@Kermit Then use `except corresponding` to replace `except`. – whymath May 18 '22 at 01:58

Deleting rows based on multiple columns conditions

1 Answers1