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"
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;
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;
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;
Thank you! It can only wok if date is numeric, what is if date is in the char format?
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.