topic Re: Joining next date with previous date repeately in SAS Programming

Joining next date with previous date repeately

carl_miles — Wed, 20 Sep 2023 10:05:29 GMT

data CustomerOrders2;
input CustomerID2 OrderDate2 :date9.;
format OrderDate2 date9.;
datalines;
1001 01JAN2023
1001 15FEB2023
1001 12DEC2023
1001 25JAN2024
;
run;

So in above code we can see that Orderdate2

I want one more column that will be next date following will come as below mentioned output i want
CustomerID2 OrderDate2 Orderdateprev

1001 01JAN2023 15FEB2023

1001 15FEB2023 12DEC2023

1001 12DEC2023 25JAN2024

1001 25JAN2024 -

So the next immediate will append and if there is none then it will be null but it will still in cohort

Could someone guide me here

Re: Joining next date with previous date repeately

Amir — Wed, 20 Sep 2023 10:43:20 GMT

Hi,

How about something like:

data want;
  merge
    customerorders2
    customerorders2(keep     = OrderDate2
                    rename   = OrderDate2 = OrderDate2next
                    firstobs = 2)
  ;
run;

Which gives:

Thanks & kind regarads,

Amir.

Re: Joining next date with previous date repeately

mkeintz — Wed, 20 Sep 2023 11:01:08 GMT

"Previous" orderdate? I hope you actually mean "next" orderdate, since "previous" suggests looking back, yet your example looks forward.

Also, I suspect your actual data has multiple ID's, and that you don't want the first orderdate of a new ID to contaminate the last record of the preceding ID. That assumption is in the code below:

data CustomerOrders2;
input CustomerID2 OrderDate2 :date9.;
format OrderDate2 date9.;
datalines;
1001 01JAN2023
1001 15FEB2023
1001 12DEC2023
1001 25JAN2024
run;

data want (drop=nxt_:);
  merge customerorders2
        customerorders2 (firstobs=2 keep=customerid2 orderdate2 
           rename=(customerid2=nxt_id orderdate2=orderdate_next));
  if nxt_id^=customerid2 then orderdate_next=.;
run;

Re: Joining next date with previous date repeately

carl_miles — Wed, 20 Sep 2023 12:22:22 GMT

Thanks mkeintz Yes My table have many records

Re: Joining next date with previous date repeately

carl_miles — Thu, 21 Sep 2023 09:29:51 GMT

data CustomerOrders2;
input CustomerID2 earlier_date OrderDate2 :date9.;
format earlier_date OrderDate2 date9.;
datalines;
1001 15dec2022 01JAN2023
1002 15dec2022 15FEB2023
1003 15dec2022 12DEC2023
1004 15dec2022 25JAN2024
;
run;

i have the dataset like this

CustomerID2 Earlier_date OrderDate2

1001 15dec2022 01JAN2023

1001 15dec2022 15FEB2023

1001 15dec2022 12DEC2023

1001 15dec2022 25JAN2024

i want output dataset to be
CustomerID2 Earlier_date OrderDate2 Orderdateprev

1001 15dec2022 01JAN2023 15dec2022

1001 15dec2022 15FEB2023 01JAN2023

1001 15dec2022 12DEC2023 15FEB2023

1001 15dec2022 25JAN2024 12DEC2023

So i want output like above like the first orderdates orderdateprev will be earlier date

the second order date's orderdateprev will be the first orderdate2 like that my flow will be

Could someone guide me here