Flag Variables

[luvscandy27]

I have the following data set below i would like to flag each month if the reg_date falls between that month.

id enter_dt reg_date startdate endate extdate
006 31JUN2017 02FEB2016 30JUN2016 30JUN2017 30SEP2018
005 31JUN2017 01JUL2017 31MAY2016 31MAY2017 31AUG2018
007 31JUL2017 25NOV2017 31JUL2016 31JUL2017 31OCT2018
001 31JAN2017 12MAR2018 31JAN2016 31JAN2017 30APR2018
002 31JAN2017 05JUN2018 29FEB2016 28FEB2017 31MAY2018
008 31AUG2017 06AUG2018 31JUL2016 31JUL2017 31OCT2018
004 31AUG2017 08FEB2018 30APR2016 30APR2017 31JUL2018
010 31MAR2017 06DEC2018 30NOV2016 30NOV2017 28FEB2019
003 31APR2017 06MAY2019 29FEB2016 28FEB2017 31MAY2018
009 31FEB2017 13AUG2019 31OCT2016 31OCT2017 31JAN2019

For instance, I would have M1-M13 (m1=jan, m2=feb, m3=march etc.) and if the reg_date falls between the startdate and endate then
their would be a flag for that ID in February. Or, if the reg_date falls between the endate and extdate then their would be a flag
for that date. For example, ID 005 reg date is in July the reg date id not fall between the start and end dates but it did fall between
the end date and extdate therefore for m7 (July) there would be a flag.

id enter_dt reg_date startdate endate extdate m1 m2 m3 m4.....m7
006 31JUN2017 02FEB2016 30JUN2016 30JUN2017 30SEP2018 0 1 0 0
005 31JUN2017 01JUL2017 31MAY2016 31MAY2017 31AUG2018 0 0 0 0 1

Any assistance would be great I tried the code below but it does not flag for a particular month.

proc sort data = test1; by reg_date ; run;

data test5;
set test1;
by reg_date;
array month{13} month_1-month_13;

do i = 1 to 13;

if startdate le reg_date le enddate then month{i} = 1;
else if extdate le reg_date le endate then month{i} = 1;
else month{i} = 0;
end;
run;

[Kurt_Bremser]

Can we simplify it to:

if regdate falls between startdate and extdate, record the month of regdate?

[luvscandy27]

Absolutely!

[novinosrin]

HI @luvscandy27   By any chance, do you mean this?

if startdate<=reg_date<=enddate then month_(month(reg_date)) = 1;
else if enddate<=reg_date<=extdate then  month_(month(reg_date)) = 1;

data want;
 set have;
 array month_{13} month_1-month_13;
 do i = 1 to 13; 
  month_(i)=0;
 end;
 if startdate<=reg_date<=enddate then month_(month(reg_date)) = 1;
else if enddate<=reg_date<=extdate then  month_(month(reg_date)) = 1;
run;

[luvscandy27]

Yes, that works thank you so much. I just have one more question is there anyway that i could get a sum of each month using the same data step or even a new data step?

[novinosrin · Accepted solution]

Hi again @luvscandy27   Do you mean this?

sum=sum(of month_1-month_13);

Full Version:

data want;
 set have;
 array month_{13} month_1-month_13;
 do i = 1 to 13; 
  month_(i)=0;
 end;
 if startdate<=reg_date<=enddate then month_(month(reg_date)) = 1;
else if enddate<=reg_date<=extdate then  month_(month(reg_date)) = 1;
sum=sum(of month_1-month_13);
run;

[luvscandy27]

Yes, thank you for all of your help.