topic Re: Selecting rows in a date range that don't also appear in a secondary date range in SAS Programming

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

buechler66 — Thu, 04 Oct 2018 18:48:31 GMT

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.

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

Tom — Thu, 04 Oct 2018 18:52:49 GMT

@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;

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

buechler66 — Thu, 04 Oct 2018 18:58:44 GMT

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

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

PGStats — Thu, 04 Oct 2018 19:02:24 GMT

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;