Collapsing Data Based On Effective and Term Dates

[mikemangini]

I have data organized as follows. For each person, there is a row for every month of the year, and a flag telling us if they had an effective policy for that month. Would I would like to do is collapse this into a single row for months where the policy was continuous, and start a new row for every break, ie after every "N" value for Policy_Effective_Flag.

So my desirable outcome from the sample data below would be:

Row 1: 123, Jan 1 2013, Mar 31 2013

Row 2: 123, Jun 1 2013, Aug 31 2013

Row 3: 123, Oct 1 2013, Dec 31 2013

Any help the community can provide would be greatly appreciated. Thank you for your assistance!

PersonID	Start_Date	Term_Date	Policy_Effective_Flag
123	Jan 1 2013	Jan 31 2013	Y
123	Feb 1 2013	Feb 28 2013	Y
123	Mar 1 2013	Mar 31 2013	Y
123	Apr 1 2013	Apr 30 2013	N
123	May 1 2013	May 31 2013	N
123	Jun 1 2013	Jun 30 2013	Y
123	Jul 1 2013	Jul 31 2013	Y
123	Aug 1 2013	Aug 31 2013	Y
123	Sep 1 2013	Sep 30 2013	N
123	Oct 1 2013	Oct 31 2013	Y
123	Nov 1 2013	Nov 30 2013	Y
123	Dec 1 2013	Dec 31 2013	Y

Mike

[Reeza · Accepted solution]

Assuming the sort is as above the following may work (untested)

data want;

set have;

by personID policy_effective_Flag NOTSORTED;

retain t_start_date t_end_date;

if first.flag then do;

     t_start_date=.; t_end_date=.;

end;

if first.flag and flag="Y" then t_start_date=start_date;

if last.flag and flag="Y" then do;

      t_end_date=term_date;

     output;

end;

run;

[mikemangini]

THANK YOU. Worked perfectly.

[Ksharp]

data have;
infile cards  expandtabs;
input (PersonID     Start_Date     Term_Date     Policy_Effective_Flag) (& $40.);
cards;
123      Jan 1 2013      Jan 31 2013      Y
123      Feb 1 2013      Feb 28 2013      Y
123      Mar 1 2013      Mar 31 2013      Y
123      Apr 1 2013      Apr 30 2013      N
123      May 1 2013      May 31 2013      N
123      Jun 1 2013      Jun 30 2013      Y
123      Jul 1 2013      Jul 31 2013      Y
123      Aug 1 2013      Aug 31 2013      Y
123      Sep 1 2013      Sep 30 2013      N
123      Oct 1 2013      Oct 31 2013      Y
123      Nov 1 2013      Nov 30 2013      Y
123      Dec 1 2013      Dec 31 2013      Y
;
run;
data want(where=(Policy_Effective_Flag='Y'));
 set have;
 by PersonID Policy_Effective_Flag notsorted;
 length first last $ 40;
 retain first ;
 if first.Policy_Effective_Flag then first=Start_Date;
 if last.Policy_Effective_Flag then do;last=Start_Date;output;end;
 drop Start_Date     Term_Date;
run;

Xia Keshan

[mikemangini]

Thank you, Xia, this solution worked as well and looks very efficient. Thank you for the support.