topic Re: Calculate number of days since last pruchase for unique clients with SAS in SAS Programming

Calculate number of days since last pruchase for unique clients with SAS

macedonataan — Sat, 12 Mar 2022 03:31:32 GMT

Hi guys, I know that this might be a naive question, but let's say that I have the following data.

Client_id Purchase_date

1 1/02/2020

2 4/02/2020

1 5/02/2020

1 7/02/2020

2 9/02/2020

1 11/02/2020

How could I use SAS create a new column with the number of days since last purchase for clients with the same ID?

The desired output would be:

Client_id Purchase_date days_since_last_purchase

1 1/02/2020 0

2 4/02/2020 0

1 5/02/2020 4

1 7/02/2020 2

2 9/02/2020 5

1 11/02/2020 4

Re: Calculate number of days since last pruchase for unique clients with SAS

pink_poodle — Sat, 12 Mar 2022 03:47:50 GMT

This is a difference in days (intk function) between date and lag(date) in groups by id and date.

Re: Calculate number of days since last pruchase for unique clients with SAS

macedonataan — Sat, 12 Mar 2022 03:54:11 GMT

Thanks for the reply.

would you be able to draft how the code would look like?

Re: Calculate number of days since last pruchase for unique clients with SAS

japelin — Sat, 12 Mar 2022 03:55:04 GMT

how about this

data have;
  input Client_id Purchase_date:mmddyy10.;
  format Purchase_date mmddyys10.;
datalines;
1  1/02/2020
2  4/02/2020
1  5/02/2020
1  7/02/2020
2  9/02/2020
1 11/02/2020
;
run;

proc sort data=have out=sorted;
  by client_id Purchase_date;
run;

data want;
  set sorted;
  by client_id Purchase_date;
  retain dt;
  if first.client_id then do;
    days_since_last_purchase=0;
  end; else
  do;
    days_since_last_purchase=intck('day',dt,Purchase_date);
  end;
  dt=Purchase_date;
  drop dt;
run;

Re: Calculate number of days since last pruchase for unique clients with SAS

pink_poodle — Sat, 12 Mar 2022 18:15:32 GMT

@japelin, could you please elaborate about the variable dt? What is it?