Hi
Below is my input code.
data input;
input ID Name$ startdate: ddmmyy10. enddate: ddmmyy10.;
format startdate: ddmmyy10. enddate: ddmmyy10.;
datalines;
132 Ryan 07/04/2017 01/03/2022
132 Ryan 02/03/2022 27/08/2022
144 Chris 11/04/2019 31/03/2022
144 Chris 01/04/2022 01/06/2022
12 May 13/09/2017 11/08/2022
12 Sandy 12/08/2022 01/10/2022;
run;
I would like my output to be when ID and Name is the same, combine them into one. Instead of the line one end date, change to line two end date
ID | Name | startdate | enddate |
132 | Ryan | 7/04/2017 | 27/08/2022 |
144 | Chris | 11/04/2019 | 1/06/2022 |
12 | May | 13/09/2017 | 11/08/2022 |
12 | Sandy | 12/08/2022 | 1/10/2022 |
Try this
data input;
input ID Name$ startdate: ddmmyy10. enddate: ddmmyy10.;
format startdate: ddmmyy10. enddate: ddmmyy10.;
datalines;
132 Ryan 07/04/2017 01/03/2022
132 Ryan 02/03/2022 27/08/2022
144 Chris 11/04/2019 31/03/2022
144 Chris 01/04/2022 01/06/2022
12 May 13/09/2017 11/08/2022
12 Sandy 12/08/2022 01/10/2022
;
data want(drop = s);
do until (last.Name);
set input;
by ID notsorted Name notsorted;
if first.Name then s = startdate;
end;
startdate = s;
run;
Result:
ID Name Startdate Enddate 132 Ryan 07/04/2017 27/08/2022 144 Chris 11/04/2019 01/06/2022 12 May 13/09/2017 11/08/2022 12 Sandy 12/08/2022 01/10/2022
You didn't actually state any conditions on the combination.
I would guess that your condition is something like 'if the startdate value is one day later than the previous enddate then combine'.
Which would raise the question, what if there are 3, 4 or more sequential records exhibiting that sequential behavior? Combine just pairs? or all 3, 4 or more?
@miss2223 wrote:
Hi
Below is my input code.
data input;
input ID Name$ startdate: ddmmyy10. enddate: ddmmyy10.;
format startdate: ddmmyy10. enddate: ddmmyy10.;datalines;
132 Ryan 07/04/2017 01/03/2022
132 Ryan 02/03/2022 27/08/2022
144 Chris 11/04/2019 31/03/2022
144 Chris 01/04/2022 01/06/2022
12 May 13/09/2017 11/08/2022
12 Sandy 12/08/2022 01/10/2022;
run;
I would like my output to be when ID and Name is the same, combine them into one. Instead of the line one end date, change to line two end date
ID Name startdate enddate 132 Ryan 7/04/2017 27/08/2022 144 Chris 11/04/2019 1/06/2022 12 May 13/09/2017 11/08/2022 12 Sandy 12/08/2022 1/10/2022
Try this
data input;
input ID Name$ startdate: ddmmyy10. enddate: ddmmyy10.;
format startdate: ddmmyy10. enddate: ddmmyy10.;
datalines;
132 Ryan 07/04/2017 01/03/2022
132 Ryan 02/03/2022 27/08/2022
144 Chris 11/04/2019 31/03/2022
144 Chris 01/04/2022 01/06/2022
12 May 13/09/2017 11/08/2022
12 Sandy 12/08/2022 01/10/2022
;
data want(drop = s);
do until (last.Name);
set input;
by ID notsorted Name notsorted;
if first.Name then s = startdate;
end;
startdate = s;
run;
Result:
ID Name Startdate Enddate 132 Ryan 07/04/2017 27/08/2022 144 Chris 11/04/2019 01/06/2022 12 May 13/09/2017 11/08/2022 12 Sandy 12/08/2022 01/10/2022
As ballarw pointed out. If there was not gap between date, you could try this one.
proc summary data=input nway; class id name; var startdate enddate; output out=want(drop=_:) min(startdate)= max(enddate)=; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.