topic Counter flag for historic months in SAS Programming

Counter flag for historic months

PetePatel — Wed, 05 Aug 2020 16:40:23 GMT

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

Re: Counter flag for historic months

PaigeMiller — Wed, 05 Aug 2020 16:08:36 GMT

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.

Re: Counter flag for historic months

ballardw — Wed, 05 Aug 2020 16:24:42 GMT

@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.

Re: Counter flag for historic months

PetePatel — Wed, 05 Aug 2020 16:31:36 GMT

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

Re: Counter flag for historic months

PetePatel — Wed, 05 Aug 2020 16:38:30 GMT

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

Re: Counter flag for historic months

PaigeMiller — Wed, 05 Aug 2020 16:46:11 GMT

/* 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.

Re: Counter flag for historic months

PetePatel — Wed, 05 Aug 2020 16:53:20 GMT

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?