create active flag based on start and end dates

[sasecn]

I am trying to get work active flags for each month during past 2 years depending on work start and end dates. My sample is like:

work_id	work_start	work_end
1	15JUN2022	15JUN2023
2	15MAY2021	10MAY2022

So, my result will add 24 work active flags from Jan. 2021 to Dec. 2022. Work is active for each month if start before last day of month and end after first of month, result looks like:

work_id	work_start	work_end	....	active_3_2022	active_4_2022	active_5_2022	active_6_2022	...
1	15MAY2022	15JUN2023	...	0	0	1	1	...
2	15MAY2021	10MAY2022	...	1	1	1	0	...

[Astounding]

Let me suggest that you change the names of the flags slightly.  For example, instead of active_3_2022 use active_2022_03.  That will simplify some of the programming statements and make your variabe names easier to sort if that is needed.

Here's an untested solution:

data want;
   set have;
   array months {24} active_2021_01 - active_2021_12 active_2022_01 - active_2202_12;
   beginning = month(work_start) + 12 * (year(work_start) - 2021);
   ending = month(work_end) + 12 * (year(work_end) - 2021);
   do j = 1 to 24;
      months{j} = (beginning <= j <= ending);
   end;
   drop j beginning ending;
run;

It looks right.  But since yoiu're the one who has the data, you will need to be the one who tests it.  If there are any problems with it, please post the log.

[sasecn · Accepted solution]

Thanks for your reply. I got an error when I tried the code. I first create the test data just like my first table:

work_id	work_start	work_end
1	15-Jun-22	15-Jun-23
2	15-May-21	10-May-22

I called it "have", when I ran the code, I got this:

Any idea, what it would be?

[Astounding]

My fault, just a typo:  active_2202_12 should be active_2022_12

[sasecn]

Sorry, my bad, I should capture that. The code works. Thanks! Some part of the code, I still don't understand, but  I think I can figure it out by some additional readings. Thanks again!