☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## create active flag based on start and end dates

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 ...
1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: create active flag based on start and end dates

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?

4 REPLIES 4
PROC Star

## Re: create active flag based on start and end dates

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.

Quartz | Level 8

## Re: create active flag based on start and end dates

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?

PROC Star

## Re: create active flag based on start and end dates

My fault, just a typo:  active_2202_12 should be active_2022_12

Quartz | Level 8

## Re: create active flag based on start and end dates

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!

Discussion stats
• 4 replies
• 437 views
• 1 like
• 2 in conversation