|
SQL self join would solve this quite easily:
data xx;
input ID Start_Date :yymmdd. End_Date :yymmdd.;
format start_date end_date date.;
cards;
123 20060401 20060406
123 20060408 20060419
123 20060420 20060430
123 20060515 20060528
184 20060501 20060512
184 20060610 20060719
184 20060920 20061130
;
** assign each obs an sequence number;
data xx;
set xx;
by id;
if first.id then n_=0;
n_+1;
run;
proc sql;
select a.id, a.start_date, a.end_date, b.start_date as bstart,
trim(put(a.n_,best.-l))||'-'||trim(put(b.n_,best.-l)) as gapname,
b.start_date - a.end_date as gap
from xx a, xx b
where a.id=b.id and b.n_ > a.n_
;
ID Start_Date End_Date bstart gapname gap
---------------------------------------------------------
123 01APR06 06APR06 08APR06 1-2 2
123 01APR06 06APR06 20APR06 1-3 14
123 01APR06 06APR06 15MAY06 1-4 39
123 08APR06 19APR06 20APR06 2-3 1
123 08APR06 19APR06 15MAY06 2-4 26
123 20APR06 30APR06 15MAY06 3-4 15
184 01MAY06 12MAY06 10JUN06 1-2 29
184 01MAY06 12MAY06 20SEP06 1-3 131
184 10JUN06 19JUL06 20SEP06 2-3 63
On Thu, 5 Oct 2006 20:48:50 -0700, Poohbear <liuberyl@GMAIL.COM> wrote:
>Hi all,
>
>I have a data set which contains 3 fields, ID, Start Date, and End
>Date.
>Some ID number has multiple entries, and I need to calculate all date
>gaps for that ID.
>
>e.g.:
>
>Obs. ID Start_Date End_Date
> 1 123 20060401 20060406
> 2 123 20060408 20060419
> 3 123 20060420 20060430
> 4 123 20060515 20060528
>.............................
>
>I need to calculate all gaps for this ID 123, not only the gaps between
>two consecutive obs..
>i.e., obs 1 and 2, 2 and 3, 3 and 4, also gaps between 1 and 3, 1 and
>4, 2 and 4.
>How can I do this in SAS, a DO loop? Any help would be highly
>appreciated!
>
>Thanks,
>Pooh
|
