SAS EG 7.1/8.2 Retain/Lag/Loop HAYULP!!!!

[EIrvin]

Hi all:

I am terrible at Retain/Lag/Loop, etc. and I am about to give up on my quest.  I have a query that I built out for someone far less experienced with SAS, but now they are asking if I can enhance the query to give them what they want. Ultimately what I am looking to do is to get a date range for each occurrence of Statement codes for a single account.  There can be a break in the continuity of those statement codes, which is making this more difficult, so for each change in the Group code I need the min EFF From Date and the max Eff To Date, therefore needing to create groups, but a single Statement Code could essentially have multiple groups.  I have tried the min and max grouping by the Statement code but that doesn't give me what I am looking for, as it gives me overlaps.  I have attached a sample of the data I have and the data I want.  

I have tried the following using Lag to identify when the missing value turns to the T and then back again.  but then I am lost.  each time there is a change that first value for that observation is missing.  What I'd like to do from there is to cut off that first group and call it like Group A, and then starting from the missing value through the next missing value call them Group B, etc etc etc.  Then I can apply the min and maxes that will give me the desired results.  Any thoughts out there in SAS LAND, please tell me there is a solution that I just have no clue about. Additionally, I am hoping that the end user doesn't have to do anything manual, except for enter the account number into the query in order to get to the results.  So no manual manipulation for the end user, like take rows 1 through X and keep those, everything else, pull into a different dataset, then combine down the road.

TIA, EIrvin

[Patrick]

Many people in this forum won't download Excels so posting here a screenshot for all to see.

[Patrick]

Does below return what you're after?

data have;
  infile datalines truncover dsd;
  input Acct_Num :$10. Statement_Code :$1. (Eff_From_Dt Eff_To_Dt) (:ddmmyy10.);
  format Eff_From_Dt Eff_To_Dt ddmmyy10.;
  datalines;
123456,,30/06/2016,27/02/2017
123456,,28/02/2017,4/12/2017
123456,,5/12/2017,8/02/2018
123456,,9/02/2018,26/06/2018
123456,T,27/06/2018,9/07/2018
123456,T,10/07/2018,13/07/2018
123456,T,14/07/2018,26/11/2018
123456,T,27/11/2018,30/12/2018
123456,T,31/12/2018,22/01/2019
123456,,23/01/2019,28/03/2019
123456,,29/03/2019,30/05/2019
123456,,31/05/2019,3/06/2019
123456,,4/06/2019,27/08/2019
123456,,28/08/2019,23/10/2019
123456,,24/10/2019,30/10/2019
123456,T,31/10/2019,10/04/2021
123456,T,11/04/2021,30/08/2021
123456,T,31/08/2021,31/12/9999
;

proc sort data=have;
  by Acct_Num Eff_From_Dt;
run;

data want(drop=_:);
  set have;
  by Acct_Num Statement_Code notsorted;
  retain _Eff_From_Dt;
  if first.Statement_Code then
    _Eff_From_Dt=Eff_From_Dt;
  if last.Statement_Code then
    do;
      Eff_From_Dt=_Eff_From_Dt;
      output;
    end;
run;