LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2006, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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?
In-Reply-To:  <>
Content-Type: text/plain; format=flowed

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

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 data set.

Also, you did not say so, so I had to guess that what you mean by 'gap' is 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.

data temp1; input Obs ID @27 Start_Date yymmdd10. @52 End_Date yymmdd10. ; format start_date end_date date9. ; cards; 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 ; run;

proc sql; create table temp2 as select a.*, 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 and a.obs>b.obs; quit;

HTH, David -- David L. Cassell mathematical statistician Design Pathways 3115 NW Norwood Pl. Corvallis OR 97330

_________________________________________________________________ Get today's hot entertainment gossip

Back to: Top of message | Previous page | Main SAS-L page