Reducing Dataset Using SAS Programming

Question

I have a dataset i.e. -

Coverage_Start  Termination_Date    Member_Id
24-Jul-19       1-Jun-21            42968701
24-Jul-19       1-Mar-21            42968701
29-Feb-20       1-Mar-20            42968701
16-Feb-19       1-Mar-19            42968701
1-Mar-17        1-Mar-18            42968701
1-Mar-16        1-Mar-17            42968701
1-Dec-15        31-Dec-16           42968701

I want to reduce this dataset, suppose in last three rows minimum coverage_start- 1-Dec-15 and maximum termination_date- 1-Mar-18, so I want to combine all three bottom rows because it has continuous coverage. As result the bottom three rows will be reduced to "1-Dec-15 1-Mar-18 42968701".

Reduced Dataset should be like -

Coverage_Start  Termination_Date    Member_Id
24-Jun-19       1-Jun-21            42968701
16-Feb-19       1-Mar-19            42968701
1-Dec-15        1-Mar-18            42968701

I want to achieve this task using SAS programming. Can anyone please help me with this? I'm trying this since a very log time but couldn't achieve it.

data _null_ data _null_ · Accepted Answer · 2021-12-16T18:01:14

This is how I would do it. I do not get same answer as you for rng 2 not sure if I don't understand or you have it wrong.

proc datasets kill nolist; quit;
/*
Coverage_Start  Termination_Date    Member_Id
29-Feb-20       1-Jun-21            42968701
16-Feb-19       1-Mar-19            42968701
1-Dec-15        1-Mar-18            42968701
*/
filename FT15F001 temp;
data cover0(keep=id date) / view=cover0;
   infile FT15F001 firstobs=2;
   input (start term) (:date.) id:$10.;
   do date=start to term;
      output;
      end;
   format date date11.;
   parmcards;
Coverage_Start  Termination_Date    Member_Id
24-Jul-19       1-Jun-21            42968701
24-Jul-19       1-Mar-21            42968701
29-Feb-20       1-Mar-20            42968701
16-Feb-19       1-Mar-19            42968701
1-Mar-17        1-Mar-18            42968701
1-Mar-16        1-Mar-17            42968701
1-Dec-15        31-Dec-16           42968701
;;;;
   run;
proc sort nodupkey data=cover0 out=cover1;
   by id date;
   run;

data cover2/ view=cover2;
   set cover1;
   by id;
   dif = dif(date);
   if first.id then do;
      dif=1;
      rng = 0;
      end;
   if dif ne 1 then rng + 1;
   run;

proc summary data=cover2 nway missing;
   class id;
   class rng / descend;
   output out=reduce(drop=_type_) min(date)=Start max(date)=Term;
   run;
proc print;
   run;

Reducing Dataset Using SAS Programming

1 Answers