Date:         Fri, 14 Apr 2006 17:07:48 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Fwd: calculate one row of a dataset, depending on another row

I like PROC SQL for this type of problem (that is, problems which require
connecting data from different rows).

Try (tested code):

   proc sql;
   create table need as
    select have.*,
           coalesce(cross.dtmCouBeg-1,have.dtmCouBeg+28)
            format ddmmyyp8. as dtmCouEnde
     from have left join have as cross
      on have.lngPatID  = cross.lngPatID and
         have.Cycle + 1 = cross.Cycle
   ;

This particular problem also has a tricky self-MERGE solution, as in
(tested code):

   data need(drop = lacycle);
   lookahead = 0;
   merge have
         have(rename=(Cycle=lacycle dtmCouBeg=dtmCouEnde)
              where=(lacycle>1)
              in=lookahead);
   by lngPatID;
   dtmCouEnde = ifn(lookahead,dtmCouEnde-1,dtmCouBeg+28);
   run;

Unusual things: (1) resetting the indicator variable LOOKAHEAD via user-
written code and (2) many-to-many relationship (ie, the note "MERGE
statement has more than one data set with repeats of BY values.", usually
a cause for concern, is to be expected here.)

On Thu, 13 Apr 2006 20:52:16 +0200, Katrin Lehmann <lehmannk@MED.UNI-
MARBURG.DE> wrote:

>Hello SAS freaks,
>
>I have the following dataset:
>
>lngPatID Cycle dtmCouBeg;
>1          1    28.03.04
>1          2    26.04.04
>1          3    01.06.04
>2          1    14.03.03
>2          2    24.04.03
>3          1    02.08.02
>3          2    05.09.02
>3          3    12.10.02
>3          4    01.11.02
>
>
>what i need is:
>
>lngPatID Cycle dtmCouBeg  dtmCouEnde;
>1          1    28.03.04  25.04.04
>1          2    26.04.04  31.05.04
>1          3    01.06.04  dtmCouBeg+28d
>2          1    14.03.03  23.04.03
>2          2    24.04.03  dtmCouBeg+28d
>3          1    02.08.02  04.09.02
>3          2    05.09.02  11.10.02
>3          3    12.10.02  31.10.02
>3          4    01.11.02  dtmCouBeg+28d;
>
>dtmCouEnde is for every lngPatID dtmCouBeg of the NEXT Cycle-1d.
>If it is the last Cycle for the lngPatID dtmCouEnde is dtmCouBeg of
>the SAME Cycle + 28d. My problem is, that I have really no idea, how to do
>this in a short way. So I'm very thankfully looking forward to your
>adwises.
>
>kind regards
>Katrin