Continuous enrollment from service date to 30 days after the service date

[skavyasindhu]

Hi everyone,

I have a wide dataset like this:

All dates are in YYYY-MM-DD format.

data have;
input member_id $ service_date coverage_start1 coverage_end1 coverage_start2 coverage_end2;
cards;
1 20150125 20130101 20131231 20150101 20150226
2 20150225 20150101 20150226 20170101 20170131
3 20161001 20160901 20161131 20161101 20161102

4 20180101 20171215 20180115 20180116 20180331
;
run;

I want only the members that have continuous enrollment from service date to 30 days after the service date

Expected output

1 20150125 20130101 20131231 20150101 20150226

3 20161001 20160901 20161131 20161101 20161102

4 20180101 20171215 20180115 20180116 20180331

TIA

[PaigeMiller · Accepted solution]

IMPORTANT CONCEPT: You make your coding MUCH MUCH MUCH easier if you treat dates as valid SAS date (or datetime) values, instead of human readable but hard-to-use 20150125. You do this by using the informat YYMMDD8. in your INPUT statement.

data have;
input member_id $ (service_date coverage_start1 coverage_end1
    coverage_start2 coverage_end2) (:yymmdd8.) ;
cards;
1 20150125 20130101 20131231 20150101 20150226
2 20150225 20150101 20150226 20170101 20170131
3 20161001 20160901 20161131 20161101 20161102
4 20180101 20171215 20180115 20180116 20180331
;
run;

Now to determine continuous coverage for 30 days beyond start, you need to compute 30 days beyond start. So the above code is modified as shown below. (As I said, once these are actual SAS date values, finding a date 30 days after a given date is a simple thing to do).

data have;
input member_id $ (service_date coverage_start1 coverage_end1 coverage_start2 coverage_end2) (:yymmdd8.) ;
service_date_plus_30=service_date + 30;
cards;
1 20150125 20130101 20131231 20150101 20150226
2 20150225 20150101 20150226 20170101 20170131
3 20161001 20160901 20161131 20161101 20161102
4 20180101 20171215 20180115 20180116 20180331
;
run;

Now continuous coverage ... the logic is that you need to have start date between (coverage_start1 and converage_end1) and / or start date between (coverage_start2 and coverage_end2); similarly service_date_plus_30 must also meet these conditions; and lastly if service_date is between coverage_start1 and coverage_end1 and service_date_plus_30 is between coverage_start2 and coverage_end2 then there can be no gap between coverage_end1 and coverage_start2.

I wrote it in words, but it should be very easy to take the words written in the previous paragraph and turn it into a SAS IF statement, so I leave that particular task up to you.