Date difference by rows based on sequence number

[Yvegunta]

Hi,

I am trying to calculate Date difference between rows for the Data as below:

Name	Date	Seq_id	Days_diff
xyz	1/01/2016	1	0
xyz	1/01/2016	2	0
abc	3/01/2016	1	2
abc	4/01/2016	2	1
abc	7/01/2016	3	3
def	10/01/2016	1	3
def	10/01/2016	2	0

I am using the query below to calculate the Days_Diff:

data Dummy5;

set Dummy4;

days_Diff = intck('days',DATE,lag(Date));

by name;

run;

As you can see in the table, the function calculates the difference in days based on the previous row (Which is fine), but does not take into consideration of Seq_id. 

Can anyone help me to make the function throw the result as below, where the difference in days restarts when Seq_id = 1:

Name	Date	Seq_id	Days_diff
xyz	1/01/2016	1	0
xyz	1/01/2016	2	0
abc	3/01/2016	1	0
abc	4/01/2016	2	1
abc	7/01/2016	3	3
def	10/01/2016	1	0
def	10/01/2016	2	0

Thank you

[Ksharp · Accepted solution]

data have;
infile cards expandtabs;
input Name $ Date : ddmmyy10. Seq_id;
format Date ddmmyy10.;
cards;
xyz	1/01/2016	1	0
xyz	1/01/2016	2	0
abc	3/01/2016	1	0
abc	4/01/2016	2	1
abc	7/01/2016	3	3
def	10/01/2016	1	0
def	10/01/2016	2
;
run;

data want;
 set have;
 dif=dif(date);
 if Seq_id=1 then dif=0;
run;

[Yvegunta]

Thank you KSharp.

It just worked perfectly.

[Yvegunta]

Hi KSharp,

Need a further help on the same. I need to retain rows where the diff is either 0,1 and also the row above.

If you look at the table below, i would only want retain only the ones marked in blue or green (as, these are either similar days or Day+1). The one marked in red, is what i want to get rid of. (Basically, i just need to retain, if current row date is same as previous row or the date is +1 of the previous row)

Name	Date	Seq_id	Diff
xyz	2-May-16	1	.
xyz	2-May-16	2	0
def	23-Mar-16	1	.
def	23-Mar-16	2	0
gef	7-Mar-16	1	.
gef	7-Mar-16	2	0
gef	31-Mar-16	3	24
gef	1-Apr-16	4	1
gef	4-Apr-16	5	3
gef	11-May-16	6	37
gef	11-May-16	7	0

Thank you

[Ksharp]

OK.

data have;
infile cards expandtabs;
input Name $ Date : date11. Seq_id;
format Date date9.;
cards;
xyz	2-May-16	1	.
xyz	2-May-16	2	0
def	23-Mar-16	1	.
def	23-Mar-16	2	0
gef	7-Mar-16	1	.
gef	7-Mar-16	2	0
gef	31-Mar-16	3	24
gef	1-Apr-16	4	1
gef	4-Apr-16	5	3
gef	11-May-16	6	37
gef	11-May-16	7	0
;
run;

data temp;
 set have;
 dif=dif(date);
 if Seq_id=1 then dif=0;
run;

data want;
 merge temp temp(keep=name dif rename=(name=_name dif=_dif) firstobs=2);
 if name=_name and dif not in (0,1) and _dif not in (0,1) then delete;
 drop _: ;
run;

[Yvegunta]

Thanks Ksharp.

The Data is not exactly as i wanted, but that is the nature of the data we can exclude. The solution you provided does most of the job done.

Brilliant. Thank you again