Merge and flag variables that fall between a specific range

[luvscandy27]

I have the following sample two datasets which I would like to merge together by ID  and add the following flags: 

completeyr would be flagged if regcomplete is between startdate and enddate 

completegrace would be flagged if regcomplete is between extdate and startdate

noncompliant would be flagged if the two conditions above are not met 

In certain instances an ID can be flagged for completeyr and completegrace, in which case I would like to

use the completeyr flag. Can anyone assist me with this? Below is an example of how I would like the

data to look. If the complete data were represented there would be several other Id's and the

other flag columns would have flags. 

data data1;
 infile datalines delimiter = ',';
 input ID $ REPORT_ORDER $ ENTERDATE:mmddyy10. MONYR:monyy7. STARTDATE:mmddyy10.
 	   ENDDATE:mmddyy10. EXTDATE:mmddyy10.;
 format ENTERDATE STARTDATE ENDDATE EXTDATE mmddyy10. MONYR:monyy7.;
 datalines;
 100,7,04/30/2019,APR2019,04/30/2018,04/30/2019,01/31/2018
 100,11,08/31/2019,AUG2019,08/31/2018,08/31/2019,05/31/2018
 100,3,12/31/2018,DEC2018,12/31/2017,12/31/2018,09/30/2017
 100,5,02/28/2019,FEB2019,02/28/2018,02/28/2019,11/30/2017
 100,4,01/31/2019,JAN2019,01/31/2018,01/31/2019,10/31/2017
 100,10,07/31/2019,JUL2019,07/31/2018,07/31/2019,04/30/2018
 200,9,06/30/2019,JUN2019,06/30/2018,06/30/2019,03/31/2018
 200,6,03/31/2019,MAR2019,03/31/2018,03/31/2019,12/31/2017
 200,8,05/31/2019,MAY2019,05/31/2018,05/31/2019,02/28/2018
 200,2,11/30/2018,NOV2018,11/30/2017,11/30/2018,08/31/2017
 200,1,10/31/2018,OCT2018,10/31/2017,10/31/2018,07/31/2017
 200,13,10/31/2019,OCT2019,10/31/2018,10/31/2019,07/31/2018
 200,12,09/30/2019,SEP2019,09/30/2018,09/30/2019,06/30/2018
 ;
 run;
 
data data2;
 infile datalines delimiter = ',';
 input ID $ MONYR:monyy7. REGCOMPLETE:mmddyy10.;
 format MONYR monyy7. REGCOMPLETE  mmddyy10.;
 datalines; 
 100,DEC2017,12/08/2017
 100,DEC2018,12/03/2018
 100,OCT2019,10/24/2019
 200,JUL2018,07/25/2018
 ;
 run;

ID	REPORT_ORDER	ENTERDATE	MONYR	STARTDATE	ENDDATE	EXTDATE	REGCOMPLETE	COMPLETEYR	COMPLETEGRACE	NONCOMPLIANT
100	3	12/31/2018	Dec-18	12/31/2017	12/31/2018	9/30/2017	12/3/2018	1
100	10	7/31/2019	Jul-19	7/31/2018	7/31/2019	4/30/2018	7/25/2018	1
200	13	10/31/2019	Oct-19	10/31/2018	10/31/2019	7/31/2018	10/24/2018	1

[PaigeMiller]

I see only one match of ID and MONYR in your tables, that at ID=100 and MONYR=DEC2018. Yet your output table seems to show three output records.

Can you explain further?

[luvscandy27]

this is the correct dataset for data2

data data2;
 infile datalines delimiter = ',';
 input ID $ MONYR:monyy7. REGCOMPLETE:mmddyy10.;
 format MONYR monyy7. REGCOMPLETE  mmddyy10.;
 datalines; 
 100,DEC2017,12/08/2017
 100,DEC2018,12/03/2018
 200,OCT2019,10/24/2019
 100,JUL2019,07/31/2018
 ;
 run;

[PaigeMiller · Accepted solution]

This code shows how to compute COMPLETEYR. If you want COMPLETEGRACE, it should be an obvious modification to the code shown.

 proc sort data=data1;
     by id monyr;
 run; 
 proc sort data=data2;
     by id monyr;
 run;
 data data3;
     merge data1 data2;
     by id monyr;
     completeyr = (startdate<=regcomplete<=enddate);
 run;

If you don't want all these mismatched records, you can delete the ones you don't want.

[luvscandy27]

Thank you!