Ranking my data with the first sales date for each customer

[_el_doredo]

Hello Experts,

I have to rank my data based on their first purchase date. If a customer purchased on 05MARCH2020 means all the observation between 05MARCH2020 and 04MARCH2021 considered as 1(1st Year). Then all the observations between 05MARCH2021 and 04MARCH2022 considered as 2(2nd Year). We need this for each customer. As first purchase date will change for all customer. Please help me with this

This is my input data

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount;
format sales_date date9.;
cards;
tom abc 07mar2019 120
tom def 25nov2019 155
tom ghi 02feb2020 110
tom jkl 07mar2020 165
tom mno 16apr2020 185
tom pqr 18oct2020 100
tom stu 05jan2021 55
tom vwx 19mar2021 67
john sdf 25jun2019 255
john vgh 05feb2020 122
john hui 15jul2020 333
john bhj 08sep2020 256
john njd 28jun2021 128
lee bhg 18oct2020 556
lee ggr 06Nov2020 546
lee njj 17oct2021 126
lee juy 20oct2021 512
lee aer 25oct2021 255
lee njk 18oct2022 596
;
run;

I want my output like this

cust_name	sales_id	sales_date	sales_amount	rank
tom	abc	07MAR2019	120	1
tom	def	25NOV2019	155	1
tom	ghi	02FEB2020	110	1
tom	jkl	07MAR2020	165	2
tom	mno	16APR2020	185	2
tom	pqr	18OCT2020	100	2
tom	stu	05JAN2021	55	2
tom	vwx	19MAR2021	67	3
john	sdf	25JUN2019	255	1
john	vgh	05FEB2020	122	1
john	hui	15JUL2020	333	2
john	bhj	08SEP2020	256	2
john	njd	28JUN2021	128	3
lee	bhg	18OCT2020	556	1
lee	ggr	06NOV2020	546	1
lee	njj	17OCT2021	126	1
lee	juy	20OCT2021	512	2
lee	aer	25OCT2021	255	2
lee	njk	18OCT2022	596	3

Thanks in Advance

[Reeza · Accepted solution]

• Sort data by customer name and date
• Use BY & Retain to store the first purchase date
• Use INTCK to calculate the difference between the sale date and the first purchase date

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount;
format sales_date date9.;
cards;
tom abc 07mar2019 120
tom def 25nov2019 155
tom ghi 02feb2020 110
tom jkl 07mar2020 165
tom mno 16apr2020 185
tom pqr 18oct2020 100
tom stu 05jan2021 55
tom vwx 19mar2021 67
john sdf 25jun2019 255
john vgh 05feb2020 122
john hui 15jul2020 333
john bhj 08sep2020 256
john njd 28jun2021 128
lee bhg 18oct2020 556
lee ggr 06Nov2020 546
lee njj 17oct2021 126
lee juy 20oct2021 512
lee aer 25oct2021 255
lee njk 18oct2022 596
;
run;

proc sort data=temp;
by cust_name sales_date;
run;

data want;
set temp;
by cust_name;
retain first_purchase;
if first.cust_name then first_purchase = sales_date;
year_of_purchase = intck('year', first_purchase, sales_date, 'C') + 1;
run;

---

@_el_doredo wrote:

Hello Experts,

I have to rank my data based on their first purchase date. If a customer purchased on 05MARCH2020 means all the observation between 05MARCH2020 and 04MARCH2021 considered as 1(1st Year). Then all the observations between 05MARCH2021 and 04MARCH2022 considered as 2(2nd Year). We need this for each customer. As first purchase date will change for all customer. Please help me with this

This is my input data

Data temp;
input cust_name $ sales_id $ sales_date date9. sales_amount;
format sales_date date9.;
cards;
tom abc 07mar2019 120
tom def 25nov2019 155
tom ghi 02feb2020 110
tom jkl 07mar2020 165
tom mno 16apr2020 185
tom pqr 18oct2020 100
tom stu 05jan2021 55
tom vwx 19mar2021 67
john sdf 25jun2019 255
john vgh 05feb2020 122
john hui 15jul2020 333
john bhj 08sep2020 256
john njd 28jun2021 128
lee bhg 18oct2020 556
lee ggr 06Nov2020 546
lee njj 17oct2021 126
lee juy 20oct2021 512
lee aer 25oct2021 255
lee njk 18oct2022 596
;
run;

I want my output like this

cust_name	sales_id	sales_date	sales_amount	rank
tom	abc	07MAR2019	120	1
tom	def	25NOV2019	155	1
tom	ghi	02FEB2020	110	1
tom	jkl	07MAR2020	165	2
tom	mno	16APR2020	185	2
tom	pqr	18OCT2020	100	2
tom	stu	05JAN2021	55	2
tom	vwx	19MAR2021	67	3
john	sdf	25JUN2019	255	1
john	vgh	05FEB2020	122	1
john	hui	15JUL2020	333	2
john	bhj	08SEP2020	256	2
john	njd	28JUN2021	128	3
lee	bhg	18OCT2020	556	1
lee	ggr	06NOV2020	546	1
lee	njj	17OCT2021	126	1
lee	juy	20OCT2021	512	2
lee	aer	25OCT2021	255	2
lee	njk	18OCT2022	596	3

Thanks in Advance

---