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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.