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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.