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 |
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.
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?
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;
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.
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.