No apology necessary, I appreciate you working through this with me. I think this is extremely close to what I'm wanting. There is only one thing I've noticed that is not coming out as I would expect. I would like the week numbers to be consistent in terms of the same week_start date should receive the same week number across persons. I believe this is an easy fix on the back end for me though, I just need to run this code after yours to produce consistent week numbers. proc sql;
create table want2 as
select *,
week(week_start) as Week_Number_Corrected
from want
;
quit; This may be easier than building a fix into your solution, if so that is fine with me. If you have an easy way to build something like that into yours I would take it though, as I will be running this for a fairly large data set and any time savings I can manage are appreciated. I'm going to do some more testing on my larger data set tonight and I will report back/accept an answer when I have verified what is happening. Thanks so much again! Edit: This might actually be a larger problem than I first realized, but we are still close. Consider the following data set. data have;
input person $1. coverage_date:mmddyy10. start_date:mmddyy10. end_date:mmddyy10.;
format coverage_date date9. start_date date9. end_date date9.;
datalines;
1 01/01/2019 01/01/2019 01/19/2019
1 02/01/2019 02/02/2019 02/28/2019
1 03/01/2019 01/01/2019 02/28/2019
2 02/01/2019 02/01/2019 02/28/2019
;
run; This marks the first weeks that person 1 and 2 were active as week 0, even though they are different times of the year. The below doesn't work syntax wise, but maybe demonstrates what I'm trying to get at. data want_not_working_code;
set have;
by person;
if first.person then do;
week_start=intnx('week',start_date,'b');
weeknumber=week(week_start);
end;
do while(week_end<end_date);
week_end=intnx('week',_s,weeknumber,'e');
Active=start_date<=week_start<=end_date or start_date<=week_end<=end_date;
output;
end;
format week_end week_start date9.;
run; Hope this is clear but am happy to answer more questions if needed.
... View more
