Brute force attack, using two data steps to merge the ends back:
data have;
input id $ start_date :date9. end_date :date9.;
format start_date end_date e8601da10.;
datalines;
XXX 30APR2019 31OCT2019
XXX 31AUG2018 28SEP2018
XXX 28FEB2018 30MAR2018
XXX 30NOV2017 29DEC2017
XXX 28APR2017 31MAY2017
XXX 31OCT2016 30NOV2016
XXX 30JUN2016 29JUL2016
XXX 31DEC2013 29AUG2014
XXX 30SEP2013 27NOV2013
XXX 31JAN2013 27FEB2013
XXX 31MAY2012 31JUL2012
XXX 26APR2011 30SEP2011
;
proc sort data=have;
by id start_date;
run;
data starts (keep=id group start_date end_date new_start_date) ends (keep=id group new_end_date);
set have;
by id;
format new_start_date new_end_date e8601da10.;
retain new_start_date group;
l_end = lag(end_date);
if first.id
then do;
new_start_date = start_date;
group = 1;
end;
else do;
if intck('year',l_end,start_date,'c') ge 1
then do;
new_end_date = l_end;
output ends;
new_start_date = start_date;
group + 1;
end;
end;
output starts;
if last.id
then do;
new_end_date = end_date;
output ends;
end;
run;
data want;
merge
starts
ends
;
by id group;
drop group;
run;
proc sort data=want;
by id descending start_date;
run;
Note how a data step with datalines makes it very easy for anybody to recreate the data I used. Please present example data in this way, so we do not have to make guesses about your data structure (types, lengths, formats).
Brute force attack, using two data steps to merge the ends back:
data have;
input id $ start_date :date9. end_date :date9.;
format start_date end_date e8601da10.;
datalines;
XXX 30APR2019 31OCT2019
XXX 31AUG2018 28SEP2018
XXX 28FEB2018 30MAR2018
XXX 30NOV2017 29DEC2017
XXX 28APR2017 31MAY2017
XXX 31OCT2016 30NOV2016
XXX 30JUN2016 29JUL2016
XXX 31DEC2013 29AUG2014
XXX 30SEP2013 27NOV2013
XXX 31JAN2013 27FEB2013
XXX 31MAY2012 31JUL2012
XXX 26APR2011 30SEP2011
;
proc sort data=have;
by id start_date;
run;
data starts (keep=id group start_date end_date new_start_date) ends (keep=id group new_end_date);
set have;
by id;
format new_start_date new_end_date e8601da10.;
retain new_start_date group;
l_end = lag(end_date);
if first.id
then do;
new_start_date = start_date;
group = 1;
end;
else do;
if intck('year',l_end,start_date,'c') ge 1
then do;
new_end_date = l_end;
output ends;
new_start_date = start_date;
group + 1;
end;
end;
output starts;
if last.id
then do;
new_end_date = end_date;
output ends;
end;
run;
data want;
merge
starts
ends
;
by id group;
drop group;
run;
proc sort data=want;
by id descending start_date;
run;
Note how a data step with datalines makes it very easy for anybody to recreate the data I used. Please present example data in this way, so we do not have to make guesses about your data structure (types, lengths, formats).
No need to aplogize, you're new here. Just make it easier for us to help you.
Using a data step with datalines to create data on the spot is a very useful SAS skill, and teaches the foundation of reading text sources.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.