Date: Thu, 5 Oct 2006 22:27:58 -0700
Reply-To: David L Cassell <davidlcassell@MSN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: David L Cassell <davidlcassell@MSN.COM>
Subject: Re: How to compare different fields of multiple records?
Content-Type: text/plain; format=flowed
>I have a data set which contains 3 fields, ID, Start Date, and End
>Some ID number has multiple entries, and I need to calculate all date
>gaps for that ID.
>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
Just as an aside, this would always be easier if you not only provide data
like the above, but also provide SAS code to read it into a working
Also, you did not say so, so I had to guess that what you mean by 'gap'
start_date - end_date - 1
so the comparison of obs 2 and 3 would be a gap of 0: they have no
missing days in between.
Rather than a data step with a do-loop and a POINT= option, I
thought PROC SQL seemed like a more natural fit for your problem.
Here's some code. I added a couple more lines just to do a little
more checking, but I still assumed the data are already sorted and
that OBS is in sorted order already within each value of ID.
input Obs ID @27 Start_Date yymmdd10. @52 End_Date yymmdd10. ;
format start_date end_date date9. ;
1 123 2006-04-01 2006-04-06
2 123 2006-04-08 2006-04-19
3 123 2006-04-20 2006-04-30
4 123 2006-05-15 2006-05-28
1 456 2006-04-09 2006-04-11
2 456 2006-04-18 2006-04-22
create table temp2 as
b.obs as obs2,
b.start_date as start2,
b.end_date as end2,
a.start_date-b.end_date-1 as gap
from temp1 as a,
temp1 as b
where a.id=b.id and a.obs>b.obs;
David L. Cassell
3115 NW Norwood Pl.
Corvallis OR 97330
Get today's hot entertainment gossip http://movies.msn.com/movies/hotgossip