Create start date-end date out of panel data

catkat96 — Mon, 22 Feb 2021 16:33:05 GMT

I have a dataset that is a panel data, that is many ID's for many moments in time. It looks like this:

ID | Date       | Start date
1  | 01/01/2021 | 01/01/2021
1  | 02/01/2021 | 01/01/2021
1  | 03/01/2021 | 01/01/2021
1  | 20/01/2021 | 20/01/2021
1  | 21/01/2021 | 20/01/2021
2  | 05/01/2021 | 05/01/2021
2  | 06/01/2021 | 05/01/2021
2  | 07/01/2021 | 05/01/2021
3  | 10/01/2021 | 10/01/2021
3  | 11/01/2021 | 10/01/2021
3  | 25/01/2021 | 25/01/2021

Where basically, Date denotes which days the ID appeared on the table, and Start date the start date of some thing happening (doesn't really matter what in this case).

I want to obtain a table like this:

ID | Start date | End date 
1  | 01/01/2021 | 03/01/2021
1  | 20/01/2021 | 21/01/2021
2  | 05/01/2021 | 07/01/2021
3  | 10/01/2021 | 11/01/2021
3  | 25/01/2021 | 25/01/2021

Where there is one line per "start date" per ID, and the "end date" is added according to the last date in which the person had that "start date".

This is my proposed code but it's clearly missing something, I think mainly how to specify that the "if" should be done for each Start_date.

DATA ArrearsPeriods;
    SET data;
    BY ID Start_date Date;
    RETAIN ID Start_date END_DATE;
    IF last.DateTHEN END_DATE = Date;
RUN;

If there's a better way of doing this in proc sql, be my guest!

Re: Create start date-end date out of panel data

Reeza — Mon, 22 Feb 2021 16:54:36 GMT

proc sql;
create table want as
select ID, start_date, max(end_date) as end_date
from have
group by ID, start_date;
quit;

topic Re: Create start date-end date out of panel data in SAS Programming

Create start date-end date out of panel data

Re: Create start date-end date out of panel data