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.
@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 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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.