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:   Fri, 6 Oct 2006 01:21:29 -0400
Reply-To:   Ya Huang <ya.huang@AMYLIN.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Ya Huang <ya.huang@AMYLIN.COM>
Subject:   Re: How to compare different fields of multiple records?
Comments:   To: liuberyl@GMAIL.COM

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


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