How to check previous rows for change in value and count the changes

[shasank]

Hi SAS Users,

Below is the data I have:

ID	Startdate	Enddate	Name
1	26-Sep-13	26-Sep-13	Erloti
1	23-Oct-13	23-Oct-13	Erloti
1	22-Nov-13	22-Nov-13	Erloti
1	16-Dec-13	16-Dec-13	Erloti
1	6-Jan-15	6-Jan-15	Erloti
1	6-Jan-15	6-Jan-15	Erloti
1	11-Feb-15	11-Feb-15	Erloti
1	11-Feb-15	11-Feb-15	Erloti
1	27-Aug-13	27-Aug-13	Erloti
1	20-Sep-13	20-Sep-13	Erloti
1	18-Oct-13	18-Oct-13	Erloti
1	20-Nov-13	20-Nov-13	Erloti
1	18-Dec-13	18-Dec-13	Erloti
1	10-Feb-15	10-Feb-15	Erloti
1	10-Feb-15	10-Feb-15	Erloti
3	1-Apr-14	1-Apr-14	Crizot
3	25-Apr-14	25-Apr-14	Crizot
3	28-May-14	28-May-14	Crizot
3	23-Jun-14	23-Jun-14	Crizot
3	15-Jul-14	15-Jul-14	Ceriti
3	11-Aug-14	11-Aug-14	Ceriti
3	3-Sep-14	3-Sep-14	Ceriti

I want to create a flag variable with the below condition

if Startdate - Lag(startdate ) > 60 or Name ne Lag(Name) then Flag = 1;

I want to create a count variable to have the total count on the Name changes per ID.

The final output expectation is below

ID	Startdate	Enddate	Name	Flag	Count
1	26-Sep-13	26-Sep-13	Erloti	0
1	23-Oct-13	23-Oct-13	Erloti	0
1	22-Nov-13	22-Nov-13	Erloti	0
1	16-Dec-13	16-Dec-13	Erloti	0
1	6-Jan-15	6-Jan-15	Erloti	1
1	6-Jan-15	6-Jan-15	Erloti	1
1	11-Feb-15	11-Feb-15	Erloti	1
1	11-Feb-15	11-Feb-15	Erloti	1
1	27-Aug-13	27-Aug-13	Erloti	1
1	20-Sep-13	20-Sep-13	Erloti	1
1	18-Oct-13	18-Oct-13	Erloti	1
1	20-Nov-13	20-Nov-13	Erloti	1
1	18-Dec-13	18-Dec-13	Erloti	1
1	10-Feb-15	10-Feb-15	Erloti	1
1	10-Feb-15	10-Feb-15	Erloti	1	1
3	1-Apr-14	1-Apr-14	Crizot	0
3	25-Apr-14	25-Apr-14	Crizot	0
3	28-May-14	28-May-14	Crizot	0
3	23-Jun-14	23-Jun-14	Crizot	0
3	15-Jul-14	15-Jul-14	Ceriti	1
3	11-Aug-14	11-Aug-14	Ceriti	1
3	3-Sep-14	3-Sep-14	Ceriti	1	2

Thank you your time and expertise.

Have a great day.

[PeterClemmensen · Accepted solution]

Like this?

data have;
input ID	(Startdate Enddate)(:date11.)	Name $;
format Startdate Enddate date11.;
datalines;
1	26-Sep-13	26-Sep-13	Erloti
1	23-Oct-13	23-Oct-13	Erloti
1	22-Nov-13	22-Nov-13	Erloti
1	16-Dec-13	16-Dec-13	Erloti
1	6-Jan-15	6-Jan-15	Erloti
1	6-Jan-15	6-Jan-15	Erloti
1	11-Feb-15	11-Feb-15	Erloti
1	11-Feb-15	11-Feb-15	Erloti
1	27-Aug-13	27-Aug-13	Erloti
1	20-Sep-13	20-Sep-13	Erloti
1	18-Oct-13	18-Oct-13	Erloti
1	20-Nov-13	20-Nov-13	Erloti
1	18-Dec-13	18-Dec-13	Erloti
1	10-Feb-15	10-Feb-15	Erloti
1	10-Feb-15	10-Feb-15	Erloti
3	1-Apr-14	1-Apr-14	Crizot
3	25-Apr-14	25-Apr-14	Crizot
3	28-May-14	28-May-14	Crizot
3	23-Jun-14	23-Jun-14	Crizot
3	15-Jul-14	15-Jul-14	Ceriti
3	11-Aug-14	11-Aug-14	Ceriti
3	3-Sep-14	3-Sep-14	Ceriti
;

data want(drop=_:);
   set have;
   by ID;
   _lagstartdate=lag1(Startdate);
   _lagname=lag1(name);
   if first.ID then do;
      _lagstartdate=.;_lagname=.;
      _count=1;flag=.;
   end;
   else do;
      if (name ne _lagname) then _count+1;
      if (Startdate-_lagstartdate>60) | (name ne _lagname) then flag=1;
   end;

   if last.ID then count=_count;
   
   format _lagstartdate date11.;
   retain flag;
run;

[shasank]

Thank you so much.

[PeterClemmensen]

Anytime, glad to help 🙂