topic Re: next date as end date by subject in New SAS User

next date as end date by subject

sonicview — Mon, 27 Sep 2021 04:20:44 GMT

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"

Re: next date as end date by subject

Jagadishkatam — Mon, 27 Sep 2021 04:43:33 GMT

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;

Re: next date as end date by subject

andreas_lds — Mon, 27 Sep 2021 05:02:36 GMT

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;

Re: next date as end date by subject

sonicview — Mon, 27 Sep 2021 05:23:35 GMT

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

Re: next date as end date by subject

Jagadishkatam — Mon, 27 Sep 2021 07:29:43 GMT

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