Collapsing Data Based On Effective and Term Dates

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Collapsing Data Based On Effective and Term Dates

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!

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

Mike


Accepted Solutions
Solution
‎05-07-2014 10:53 AM
Super User
Posts: 17,826

Re: Collapsing Data Based On Effective and Term Dates

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;

View solution in original post


All Replies
Solution
‎05-07-2014 10:53 AM
Super User
Posts: 17,826

Re: Collapsing Data Based On Effective and Term Dates

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;

Contributor
Posts: 34

Re: Collapsing Data Based On Effective and Term Dates

THANK YOU. Worked perfectly.

Super User
Posts: 9,681

Re: Collapsing Data Based On Effective and Term Dates

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

Contributor
Posts: 34

Re: Collapsing Data Based On Effective and Term Dates

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 199 views
  • 5 likes
  • 3 in conversation