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
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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.