topic Re: Distance between days in SAS Programming

Distance between days

zana — Tue, 13 May 2014 19:31:02 GMT

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.

Re: Distance between days

PGStats — Tue, 13 May 2014 20:09:05 GMT

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;

Re: Distance between days

Haikuo — Tue, 13 May 2014 21:00:39 GMT

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

Re: Distance between days

PGStats — Tue, 13 May 2014 22:03:39 GMT

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

Re: Distance between days

data_null__ — Tue, 13 May 2014 22:44:22 GMT

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; 

Re: Distance between days

Haikuo — Tue, 13 May 2014 23:31:25 GMT

Awesome, PG!

Re: Distance between days

slchen — Wed, 14 May 2014 00:51:54 GMT

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;

Re: Distance between days

zana — Wed, 14 May 2014 15:14:27 GMT

Dear PG,

Hardly thanks for you, _null and Hai.

Best regards.