BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yvegunta
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

5 REPLIES 5
Ksharp
Super User
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
Obsidian | Level 7

Thank you KSharp.

It just worked perfectly.

Yvegunta
Obsidian | Level 7

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

Ksharp
Super User

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
Obsidian | Level 7

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

 

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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