You can use this code which gives your desired output
Flow_typ
Region
Account_Status
Amount
St_date
end_date
External
North
Closed
500
1/01/2024
01/31/2024
External
North
Closed
500
3/01/2026
05/31/2026
External
North
Closed
600
2/01/2024
02/28/2024
Internal
South
Open
100
1/01/2020
01/31/2020
Internal
South
Open
200
2/01/2021
02/28/2021
Internal
South
Open
200
5/01/2030
07/31/2030
proc sort data = test1 out = test1;
by Flow_typ Region Account_Status Amount St_date end_date ;
run;
/*array to compare previous vales to identify consecutive days*/
data test2;
set test1;
by Flow_typ Region Account_Status Amount ;
array sdate(1); format sdate1 mmddyy10.; array edate(1); format edate1 mmddyy10.;
if first.amount then do; i=1 ;
sdate(1)=st_date; edate(1)=end_date;
NewGroup=1; end;
if i ne 1 then do;
if st_date ne edate(1)+1 then do;
sdate(1)=st_date; edate(1)=end_date; NewGroup=2; end;
else if i ne 1 and st_date = edate(1)+1 then do; edate(1) = end_date; end;
else NewGroup+1;
end;
i + 1;
retain sdate1 edate1 ;
drop i;
run;
Proc sql;
Create table test3 as
Select distinct
Flow_typ, Region, Account_Status, Amount, min(sdate1) as St_date format mmddyy10., Max(edate1) as end_date format mmddyy10.
from
test2
group by Flow_typ, Region, Account_Status, Amount,NewGroup /*instead of NewGroup sdate1*/
;Quit;
... View more