Help using Base SAS procedures

Date difference by rows based on sequence number

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Date difference by rows based on sequence number

Hi,

 

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

 

NameDateSeq_idDays_diff
xyz1/01/201610
xyz1/01/201620
abc3/01/201612
abc4/01/201621
abc7/01/201633
def10/01/201613
def10/01/201620

 

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:

 

NameDateSeq_idDays_diff
xyz1/01/201610
xyz1/01/201620
abc3/01/201610
abc4/01/201621
abc7/01/201633
def10/01/201610
def10/01/201620

 

Thank you


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

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;

View solution in original post


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

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: 13

Re: Date difference by rows based on sequence number

Thank you KSharp.

It just worked perfectly.

Occasional Contributor
Posts: 13

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)

 

NameDateSeq_idDiff
xyz2-May-161.
xyz2-May-1620
def23-Mar-161.
def23-Mar-1620
gef7-Mar-161.
gef7-Mar-1620
gef31-Mar-16324
gef1-Apr-1641
gef4-Apr-1653
gef11-May-16637
gef11-May-1670

 

 

Thank you

Super User
Posts: 10,028

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: 13

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.

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

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