## Distance between days

Solved
Frequent Contributor
Posts: 81

# Distance between days

Dear All:

Could you please how to calculate the distance between days from the date the workshop first to a given date and the number of days to next date?

HAVE

Workshop-id       Date

A              05/17/2012

A              05/27/2012

A              06/09/2012

B              11/04/2013

B              01/14/2014

C              10/10/2011

C              12/04/2011

C              01/24/2014

WANT

Workshop-id       Date           Difference

A                05/17/2012            10

A                05/27/2012            12

A                06/09/2012            0

B                11/04/2013            70

B                01/14/2014            0

C                10/10/2011            54

C                12/04/2011            30

C                01/04/2012            0

Best regards.

Accepted Solutions
Solution
‎05-13-2014 04:09 PM
Posts: 5,526

## Re: Distance between days

Try this:

proc sort data=have; by workshopId descending date; run;

data want;

set have; by workshopId;

prevDate = lag(date);

if first.workshopId then difference = 0;

else difference = intck("DAY", date, prevDate);

drop prevDate;

run;

proc sort data=want; by workshopId date; run;

PG

PG

All Replies
Solution
‎05-13-2014 04:09 PM
Posts: 5,526

## Re: Distance between days

Try this:

proc sort data=have; by workshopId descending date; run;

data want;

set have; by workshopId;

prevDate = lag(date);

if first.workshopId then difference = 0;

else difference = intck("DAY", date, prevDate);

drop prevDate;

run;

proc sort data=want; by workshopId date; run;

PG

PG
Frequent Contributor
Posts: 81

## Re: Distance between days

Dear PG,

Hardly thanks for you, _null and Hai.

Best regards.

Posts: 3,167

## Re: Distance between days

Within the regime of data step, you could also try using look-ahead technique, but PG's code is the best for beginners to get their feet wet.

Below is to use Proc SQL as an alternative:

data have;

input Workshop_id:\$       Date:mmddyy10.;

format date mmddyy10.;

cards;

A              05/17/2012

A              05/27/2012

A              06/09/2012

B              11/04/2013

B              01/14/2014

C              10/10/2011

C              12/04/2011

C              01/04/2012

;

proc sql;

select *, (select intck('day',a.date,b.date) from have b where a.workshop_id=b.workshop_id and a.date < b.date

group by b.workshop_id having b.date=min(b.date)) as Difference

from have a

;

quit;

Haikuo

Posts: 5,526

## Re: Distance between days

coalesce((select....), 0) as Difference - to get the zeros.

PG
Posts: 3,167

Awesome, PG!

Posts: 3,852

## Re: Distance between days

data workshop;
input id :\$1.   Date :mmddyy.;

format date mmddyy.;

cards;
A              05/17/2012
A              05/27/2012
A              06/09/2012
B              11/04/2013
B              01/14/2014
C              10/10/2011
C              12/04/2011
C              01/24/2014
;;;;
run;
data workshop;
set workshop end=eof;
by id;
if not eof then set workshop(firstobs=2 keep=date rename=(date=Next));
if not last.id

then dif=range(date,next);
else dif=0;

drop next;
run;
proc print;

run;
Super Contributor
Posts: 275

## Re: Distance between days

data workshop;

input id :\$1.   Date :mmddyy.;

format date mmddyy.;

cards;

A              05/17/2012

A              05/27/2012

A              06/09/2012

B              11/04/2013

B              01/14/2014

C              10/10/2011

C              12/04/2011

C              01/24/2014

;;;;

run;

data want;

merge workshop workshop(firstobs=2 rename=(id=_id date=_date));

difference=0;

if id=_id then difference=_date-date;

drop _:;

run;

🔒 This topic is solved and locked.