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