SQL To Delete Observations by Key and Date Range

deleted_user — Thu, 23 Jul 2009 17:00:29 GMT

I have two different SAS datasets setup like the following...

Dataset1
ID Date
1 31/01/2009
1 28/02/2009
1 30/03/2009
...

Dataset2
1 StartDate EndDate
1 28/02/2009 29/03/2009

I wish to delete from dataset1 where StartDate (LE) date (LE) EndDate to leave the following

Dataset1
1 31/01/2009
1 30/03/2009
...

I've managed to come up with something that worked....
proc sql;
create table dataset1 as
select a.*
from dataset1 as a
left join
dataset2 as b
on a.id=b.id and (b.startdate LE a.date LE b.enddate)
having missing(b.id)
order by a.id, a.date
;
quit;

However, I was wondering if there was something that I could do in a "DELETE FROM" way?

I noticed that SQL could use the "DELETE FROM... USING" but couldn't get it to work.

Thanks in advance.

Message was edited by: Superman Message was edited by: Superman

Re: SQL To Delete Observations by Key and Date Range

deleted_user — Thu, 23 Jul 2009 17:03:27 GMT

Sorry, double post. Message was edited by: Superman

topic Re: SQL To Delete Observations by Key and Date Range in SAS Procedures

SQL To Delete Observations by Key and Date Range

Re: SQL To Delete Observations by Key and Date Range