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
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;
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;
THANK YOU. Worked perfectly.
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
Thank you, Xia, this solution worked as well and looks very efficient. Thank you for the support.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.