Following your logic as I understand it, try this: data have; infile datalines delimiter=','; informat Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit; format Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit; input Agreement Effective_Date1 Debit Credit; datalines; A1,01/02/2020,100,0 A1,01/02/2020,632,0 A1,01/02/2020,0, 100 A1,01/02/2020,0,632 A1,01/02/2020,100,0 A1,01/02/2020,632,0 A1,01/02/2020,0,3 A1,01/02/2020,3,0 A1,22/02/2020,3,0 A2,02/03/2020,50,0 ;; data expanded; set have; n = _n_; cr = (credit not in (0,.)); amount = sum(debit,credit); run; proc sort data=expanded; by agreement effective_date1 cr amount n; run; data counted; set expanded; by agreement effective_date1 cr amount; array ct (0:1) count_debit count_credit; if first.cr then do; ct{0} = 0; ct{1} = 0; end; if first.amount then ct{cr} = 1; else ct{cr} + 1; run; proc sort data=counted out=want (drop=cr n) ; by n; run; ... View more

Are you coming from Europe ? Germany ?   data have; infile cards truncover expandtabs; input Agreement $ Effective_Date1 : ddmmyy10. (Debit Credit) (: commax32.); format Effective_Date1 ddmmyy10.; cards; A1 10/09/2019 0 30 A1 11/09/2019 0 750 A1 11/09/2019 0 750 A1 11/09/2019 0 260,75 1 A1 11/09/2019 260,75 0 1 A1 11/09/2019 260,75 0 A1 11/09/2019 260,75 0 A1 11/09/2019 260,75 0 A2 04/08/2020 0 1 A2 11/08/2020 150 0 A2 11/08/2020 150 0 2 A2 11/08/2020 150 0 3 A2 11/08/2020 0 150 2 A2 11/08/2020 0 150 3 A3 12/09/2020 100 0 A3 21/09/2020 100 0 5 A3 21/09/2020 100 0 4 A3 21/09/2020 0 100 4 A3 21/09/2020 0 100 5 A3 21/09/2020 0 100 ; data debit(drop=credit) credit; set have; if debit=0 then do;debit=credit;output credit;end; if credit=0 then output debit; run; proc sort data=debit;by Agreement Effective_Date1 debit;run; proc sort data=credit;by Agreement Effective_Date1 debit;run; data temp; ina=0;inb=0; merge debit(in=ina) credit(in=inb); by Agreement Effective_Date1 debit; if not ina then debit=0; if not inb then credit=0; if ina and inb then flag=1; run; data temp; set temp; if flag then n+1; run; data want; set temp; if flag then do;dummy=n;debit=0;output;debit=credit;credit=0;output;end; else output; drop flag n; run; ... View more

OK, so your results should look like this, yes?   There might be multiple ways to do this, but I would use an array to hold the start and end dates and then index through the array looking for dates that fell between each start and end date range.  Sample code is below.   Jim   DATA have1; LENGTH ID 3; informat Start_Date End_Date ddmmyy10.; format Start_Date End_Date ddmmyy10.; input id Start_Date End_Date; CALL SYMPUTX ('Nbr_Of_Dates', _N_, 'G'); datalines; 10 08/02/2020 07/03/2020 10 02/10/2020 18/10/2020 ;;;; RUN; **------------------------------------------------------------------------------**; DATA have2; LENGTH ID 3; infile datalines dsd delimiter=','; informat Date ddmmyy10.; format Date ddmmyy10.; input id Date ; datalines; 10,01/01/20 10,15/02/20 10,01/04/20 10,05/05/20 10,05/10/20 ;;;; RUN; **------------------------------------------------------------------------------**; DATA want; DROP _:; FORMAT ID Date; IF _N_ = 1 THEN DO; DO _i = 1 TO &Nbr_Of_Dates; SET Have1 (RENAME=(ID = _ID)); ARRAY IDs [&Nbr_Of_Dates] _TEMPORARY_; ARRAY Starts [&Nbr_Of_Dates] _TEMPORARY_; ARRAY Ends [&Nbr_Of_Dates] _TEMPORARY_; IDs[_i] = _ID; Starts[_i] = Start_Date; Ends[_i] = End_Date; * PUTLOG "NOTE: " _i= Starts[_i]= Ends[_i]=; END; END; SET Have2; _Match = 0; DO _i = 1 TO &Nbr_Of_Dates; IF ID = IDs[_i] THEN DO; IF Starts[_i] < Date < Ends[_i] THEN DO; _Match = 1; Start_Date = Starts[_i]; End_Date = Ends[_i]; OUTPUT; END; ELSE DO; END; END; ELSE DO; END; END; IF NOT _Match THEN DO; CALL MISSING(Start_Date, End_Date); OUTPUT; END; RUN; ... View more