next date as end date by subject

[sonicview]

Hi All,

I have a customer dataset with the dates. Each customer ordered multiple times. I want to create an end date based on the next order date.

For the last order of a customer, his end date will be the same as the order date.

If custome have only one order then his end date will be the same as the order date.

Here is the example.

Customer_id     date 

111                   02JAN2021

111                   05JAN2021

111                  10JAN2021 

222                  08JAN2021

333                 12MAR2021

333                 18MAR2021

The output will be

Customer_id     date                           end_date

111                   02JAN2021              05JAN2021

111                   05JAN2021              10JAN2021

111                  10JAN2021               10JAN2021 

222                  08JAN2021               08JAN2021

333                  12MAR2021              18MAR2021

333                   18MAR2021             18MAR2021

"Thank you"

[Jagadishkatam · Accepted solution]

data have;
input Customer_id date :date9.;
cards;
111 02JAN2021
111 05JAN2021
111 10JAN2021 
222 08JAN2021
333 12MAR2021
333 18MAR2021
;

data want;
merge have have(keep=date rename=(date=date2) firstobs=2);
run;

proc sort data=want;
by Customer_id;
run;

data want2;
set want;
by Customer_id;
if last.Customer_id then date2=date;
format date: date9.;
run;

[andreas_lds]

Another solution:

proc sort data=have out=sorted;
   by Customer_id descending date;
run;

data want;
   set sorted;
   by customer_id;
   
   format end_date date9.;
   
   last_date = lag(date);
   end_date = ifn(first.customer_id, date, last_date);
   
   drop last_date;
run;

proc sort data=want;
   by customer_id date;
run;
   

[sonicview]

Thank you! It can only wok if date is numeric, what is if date is in the char format?

[Jagadishkatam]

We then have to convert the character date to numeric date and follow the logic.