BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sonicview
Fluorite | Level 6

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"

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16
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;
Thanks,
Jag

View solution in original post

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16
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;
Thanks,
Jag
andreas_lds
Jade | Level 19

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
Fluorite | Level 6

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

Jagadishkatam
Amethyst | Level 16
We then have to convert the character date to numeric date and follow the logic.
Thanks,
Jag

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 1021 views
  • 5 likes
  • 3 in conversation