Date:         Mon, 27 Oct 2008 23:50:01 -0400
Reply-To:     kwu0914 <kwu0914@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         kwu0914 <kwu0914@GMAIL.COM>
Organization: Aioe.org NNTP Server
Subject:      Re: Coding help required.
Comments: To: sas-l@uga.edu

If understand you correctly, the consecutive month = the difference between
two dates
is less than 30 days and they are consecutive in input data set. Try
following:


data in;
   input SrvDte: date9.      DaysGap    ConsecMths;
   cards;
                    10AUG2007      0                 1
                    26SEP2007     47                1
                    04OCT2007      8                 2
                    05OCT2007      1                 3
                    06NOV2007     32                1
                    29NOV2007     23                2
                   11DEC2007      12                3
                   27DEC2007      16                4
                   24JAN2008       28                5
                   04FEB2008      11                 6
                   18MAR2008      43                1
                   02MAY2008      45                1
                    03JUL2008      62                 1
;
run;

proc sort data = in;
    by SrvDte ConsecMths;
run;

data tmp;  /* Group consecutive months */
     set in; by SrvDte Consecmths;
  if _n_ = 1 then gp = 1; /*initialize gp*/
  if  ceil((SrvDte - lag(SrvDte) ) /30) <2 then output; /* consecutive month
found */
  else do; gp +1; output; end; /* Not consecutive month, start a new group
*/
run;

proc sql;
    select a.SrvDte, a.DaysGap, a.ConsecMths from tmp a,
     (select gp,count(*) as cnt from tmp  /* subquery find the group which
has 6 consecutive records */
  group by gp) b
  where a.gp=b.gp and b.cnt=6;
quit;

proc datasets lib = work; /* Clean up */
    delete tmp in;
quit;


"Phil" <phillip.anderra@gmail.com> wrote in message
news:d82f1c58-7083-4969-96e5-0334de8facfa@j22g2000hsf.googlegroups.com...
> Hullo nice group,
>
> I need to extract *only* the rows below where the row is part of a
> sequence of >= six Consecutive Months. eg. From the dataset below
> there'd be a total of six rows output. It's proving too hard for me so
> if anyone could help it'd be appreciated.
>
>
> PIN               SrvDte       DaysGap    ConsecMths
> 2999128       10AUG2007      0                 1
>                    26SEP2007     47                1
>                    04OCT2007      8                 2
>                    05OCT2007      1                 3
>                    06NOV2007     32                1
>                    29NOV2007     23                2
>                   11DEC2007      12                3
>                   27DEC2007      16                4
>                   24JAN2008       28                5
>                   04FEB2008      11                 6
>                   18MAR2008      43                1
>                   02MAY2008      45                1
>                    03JUL2008      62                 1
>
>
>
> cheers
> Phil