Distance between days

[zana]

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.

[PGStats · Accepted solution]

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

[zana]

Dear PG,

Hardly thanks for you, _null and Hai.

Best regards.

[Haikuo]

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

[PGStats]

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

[Haikuo]

Awesome, PG!

[data_null__]

You can look ahead.

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;

[slchen]

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;