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 |
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.
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
@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.
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
/* 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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.