topic Re: How to check previous rows for change in value and count the changes in SAS Data Management

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

shasank — Mon, 06 May 2019 15:54:50 GMT

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.

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

PeterClemmensen — Mon, 06 May 2019 16:13:54 GMT

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;

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

shasank — Mon, 06 May 2019 16:26:44 GMT

Thank you so much.

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

PeterClemmensen — Mon, 06 May 2019 17:46:06 GMT

Anytime, glad to help 🙂