Distance between days

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

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
Respected Advisor
Posts: 4,662

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

View solution in original post


All Replies
Solution
‎05-13-2014 04:09 PM
Respected Advisor
Posts: 4,662

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.

Respected Advisor
Posts: 3,124

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

Respected Advisor
Posts: 4,662

Re: Distance between days

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

PG
Respected Advisor
Posts: 3,124

Re: Distance between days

Awesome, PG!

Respected Advisor
Posts: 3,777

Re: Distance between days

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

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

Discussion stats
  • 7 replies
  • 280 views
  • 7 likes
  • 5 in conversation