topic Re: How to find first_purchase_date(fpd) and last_purchase_date(lpd) from a given multiple transacti in SAS Programming

How to find first_purchase_date(fpd) and last_purchase_date(lpd) from a given multiple transactions

subhani4 — Mon, 03 May 2021 18:04:29 GMT

i have a below dataset

data temp;
input cust dt :date9.;
format dt date9.;
datalines;
1 01apr2021
1 02apr2021
1 10apr2021
2 15apr2021
2 18apr2021
2 20apr2021
2 25apr2021
3 29apr2021
3 03may2021
4 04may2021
;
run;

and i want a single entry for each cusotmer with fpd and lpd transactions

ex: output should be

cust fpd lpd

1 01apr2021 10apr2021

2 15apr2021 25apr2021

3 29apr2021 03may2021

4 04may2021 04may2021

i already have sql query for the same i want the solution in data step. Thanks in advance!!

sql query :

proc sql;
select cust,min(dt) as fpd format date9.,max(dt) as lpd format date9. from temp
group by cust;
quit;

Re: How to find first_purchase_date(fpd) and last_purchase_date(lpd) from a given multiple transacti

ballardw — Mon, 03 May 2021 18:04:49 GMT

One way:

proc summary data=temp nway;
   class cust;
   var dt;
   output out=want (drop=_: ) min=Fpd max=lpd;
run;

Re: How to find first_purchase_date(fpd) and last_purchase_date(lpd) from a given multiple transacti

Ksharp — Tue, 04 May 2021 10:34:43 GMT

Assuming the data has been sorted .


data temp;
input cust dt :date9.;
format dt date9.;
datalines;
1 01apr2021
1 02apr2021
1 10apr2021
2 15apr2021
2 18apr2021
2 20apr2021
2 25apr2021
3 29apr2021
3 03may2021
4 04may2021
;
run;
data want;
do until(last.cust);
 set temp;
 by cust;
 if first.cust then first=dt;
end;
last=dt;
drop dt;
format first last date9.;
run;