BookmarkSubscribeRSS Feed
PetePatel
Quartz | Level 8

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?

 

IDDateCarCar_flag3MSC
1Jan-06000
1Feb-06000
1Mar-06000
1Apr-06000
1May-06000
1Jun-06000
1Jul-060.711
1Aug-06012
1Sep-06111
1Oct-061.311
1Nov-06012
1Dec-06013
2Feb-14010
2Mar-14000
2Apr-14000
2May-14000
2Jun-140.611
2Jul-14012
2Aug-14013
2Sep-14014
2Oct-14005
2Nov-14211
3Aug-18000
3Sep-18000
3Oct-18000
3Nov-18000
3Dec-18000

 

 

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
PetePatel
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

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
Diamond | Level 26
/* 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.

--
Paige Miller
PetePatel
Quartz | Level 8

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?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 735 views
  • 0 likes
  • 3 in conversation