topic Re: Combine two line into one based in conditions in New SAS User

Combine two line into one based in conditions

miss2223 — Wed, 24 Aug 2022 04:24:55 GMT

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

Re: Combine two line into one based in conditions

ballardw — Wed, 24 Aug 2022 07:21:51 GMT

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

Re: Combine two line into one based in conditions

PeterClemmensen — Wed, 24 Aug 2022 08:05:26 GMT

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

Re: Combine two line into one based in conditions

Ksharp — Wed, 24 Aug 2022 12:15:50 GMT

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;