I have the following sample two datasets which I would like to merge together 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
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;
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.
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 |
Merge the two datasets by what criteria??
Please do not post duplicate threads.
DO NOT REPLY FURTHER HERE
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.