Solved
Contributor
Posts: 36

# 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: 23,685

## 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;

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

## 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: 36

## Re: Collapsing Data Based On Effective and Term Dates

THANK YOU. Worked perfectly.

Super User
Posts: 10,770

## 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: 36

## 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 and locked.