## Date difference by rows based on sequence number

Solved
Occasional Contributor
Posts: 14

# Date difference by rows based on sequence number

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

Accepted Solutions
Solution
‎06-29-2016 06:55 PM
Super User
Posts: 10,778

## Re: Date difference by rows based on sequence number

``````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;``````

All Replies
Solution
‎06-29-2016 06:55 PM
Super User
Posts: 10,778

## Re: Date difference by rows based on sequence number

``````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;``````
Occasional Contributor
Posts: 14

## Re: Date difference by rows based on sequence number

Thank you KSharp.

It just worked perfectly.

Occasional Contributor
Posts: 14

## Re: Date difference by rows based on sequence number

[ Edited ]

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

Super User
Posts: 10,778

## Re: Date difference by rows based on sequence number

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;``````
Occasional Contributor
Posts: 14

## Re: Date difference by rows based on sequence number

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 5 replies
• 363 views
• 0 likes
• 2 in conversation