Selecting rows in a date range that don't also appear in a secondary date range

[buechler66]

Hi all. 

I have a dataset with two variables, MY_ID and MY_DATE.  The record MY_DATEs are all between the two year period of '01JUN2016'D AND '31MAY2018'D. 

Using Proc SQL, I need to pull the IDs that are present with MY_DATEs between '01JUN2017'D AND '31MAY2018'D, but NOT also present between '01JUN2016'D AND '31MAY2017'D.

Does anyone have a suggest for a good way to code this?  Any help would be greatly appreciated.

[Tom · Accepted solution]

---

@buechler66 wrote:

Hi all. 

I have a dataset with two variables, MY_ID and MY_DATE.  The record MY_DATEs are all between the two year period of '01JUN2016'D AND '31MAY2018'D. 

Using Proc SQL, I need to pull the IDs that are present with MY_DATEs between '01JUN2017'D AND '31MAY2018'D, but NOT also present between '01JUN2016'D AND '31MAY2017'D.

Does anyone have a suggest for a good way to code this?  Any help would be greatly appreciated.

---

Should be straight forward to translate your problem statement into SQL

/* Using Proc SQL, I need to pull
 the IDs that are present with MY_DATEs between '01JUN2017'D AND '31MAY2018'D
, but NOT also present between '01JUN2016'D AND '31MAY2017'D.
*/
proc sql ;
 select distinct MY_ID from have 
 where MY_DATE between '01JUN2017'D AND '31MAY2018'D

 except 

 select distinct MY_ID from have 
 where MY_DATE between '01JUN2016'D AND '31MAY2017'D
;
quit;

[buechler66]

Thanks so much. I appreciate you taking the time to help. Much appreciated.

[PGStats]

I would propose:

proc sql ;
 select * from have as a
 where  
    MY_DATE between '01JUN2017'D AND '31MAY2018'D and
    not exists (    select * from have 
                    where   My_ID = a.MY_ID and 
                            MY_DATE between '01JUN2016'D AND '31MAY2017'D )
;
quit;