Counter flag for historic months

[PetePatel]

Hi,

I have a dataset with variables ID,  Date and Car.

I want to create the following two new variables by ID:

Car_flag3: Where car>0 in the last 3 months then Car_flag=1; else car_flag=0

MSC: Counter for number of months since car>1

How can I create these new variables efficiently?

ID	Date	Car	Car_flag3	MSC
1	Jan-06	0	0	0
1	Feb-06	0	0	0
1	Mar-06	0	0	0
1	Apr-06	0	0	0
1	May-06	0	0	0
1	Jun-06	0	0	0
1	Jul-06	0.7	1	1
1	Aug-06	0	1	2
1	Sep-06	1	1	1
1	Oct-06	1.3	1	1
1	Nov-06	0	1	2
1	Dec-06	0	1	3
2	Feb-14	0	1	0
2	Mar-14	0	0	0
2	Apr-14	0	0	0
2	May-14	0	0	0
2	Jun-14	0.6	1	1
2	Jul-14	0	1	2
2	Aug-14	0	1	3
2	Sep-14	0	1	4
2	Oct-14	0	0	5
2	Nov-14	2	1	1
3	Aug-18	0	0	0
3	Sep-18	0	0	0
3	Oct-18	0	0	0
3	Nov-18	0	0	0
3	Dec-18	0	0	0

[PaigeMiller]

I want to create the following two new variables:

Car_flag3: Where car_flag>0 in the last 3 months then Car_flag=1; else car_flag=0

MSC: Counter for number of months since car_flag>0

Your text does not match your variable names. Your text refers to variable car_flag, but there is no such variable. Please clarify this.

[PetePatel]

Sorry @PaigeMiller I meant:

I want to create the following two new variables:

Car_flag3: Where car>0 in the last 3 months then car_flag=1; else car_flag=0

MSC: Counter for number of months since car>0

[ballardw]

---

@PetePatel wrote:

Hi,

I have a dataset with variables ID,  Date and Car.

I want to create the following two new variables:

Car_flag3: Where car_flag>0 in the last 3 months then Car_flag=1; else car_flag=0

MSC: Counter for number of months since car_flag>0

How can I create these new variables efficiently?

Thanks

Example ID's below with expected outputs:

ID	Date	Car	Car_flag3	MSC
1	Jan-06	0	0	0
1	Feb-06	0	0	0
1	Mar-06	0	0	0
1	Apr-06	0	0	0
1	May-06	0	0	0
1	Jun-06	0	0	0
1	Jul-06	0.7	1	1
1	Aug-06	0	1	2
1	Sep-06	1	1	1
1	Oct-06	1.3	1	1
1	Nov-06	0	1	2
1	Dec-06	0	1	3
2	Feb-14	0	1	4
2	Mar-14	0	0	5
2	Apr-14	0	0	6
2	May-14	0	0	7
2	Jun-14	0.6	1	1
2	Jul-14	0	1	2
2	Aug-14	0	1	3
2	Sep-14	0	1	4
2	Oct-14	0	0	5
2	Nov-14	2	1	1
3	Aug-18	0	0	0
3	Sep-18	0	0	0
3	Oct-18	0	0	0
3	Nov-18	0	0	0
3	Dec-18	0	0	0

---

Have to assume that you mean to use the variable shown as CAR instead of car_flag since the example data has no "car_flag".

Since you show car_flag3 =1 for Jul-06 then you need to clarify that your rule is not ": Where car_flag>0 in the last 3 months" but actually ": Where car_flag>0 in the current or previous 3 months".

You also do not specify whether the "3 months" all have to be in the same ID. Should it?

]

MSC: Counter for number of months since car_flag>0 is worded poorly. "Since" usually implies different than the current value but you are counting the current value of car_flag >1 as "one month since" where the more typical interpretation would that the current month would be 0.

Your values for ID=2 and date=Feb-14 make me think that you not actually applied your rules correctly in the example.

[PetePatel]

sorry @ballardw I meant:

I want to create the following two new variables:

Car_flag3: Where car>0 in the last 3 months then car_flag=1; else car_flag=0

MSC: Counter for number of months since car>1

The three months should all have to be the same ID

[PaigeMiller]

/* UNTESTED CODE */
/* Assumes data set HAVE is properly sorted by ID */
data want;
    set have;
    by id;
    car_flag3=(car>0 or lag(car)>0 or lag2(car)>0 or lag3(car)>0) and
        (lag3(id)=id or _n_<3);
    if first.id then msc=0;
    if car>0 then msc+1;
run;

In the future, please data as SAS data steps, following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/  That's the only way we can provide tested solutions. We can't provide tested solutions on data posted as a screen capture.

[PetePatel]

Thanks @PaigeMiller the car_flag3 variable works perfectly!

MSC should continue counting as it's a months since car>0.

For example, if car>0 in Feb-14 for a particular ID then MSC=1
in Mar-14 MSC=2 if car=0
Apr-14 MSC=3 if car=0

In May-14 if car>0 again for that ID then the MSC counter starts from 1 again.

Can that be incorporated into your code?