I'm looking to calculate YoY growth rate of a few products that fall into different categories. Below is a sample data
Year | Category | Product | Product_ID | sales |
2022 | Dairy | Milk | 101 | 1200 |
2022 | Dairy | Plain Yogurt | 102 | 1434.5 |
2022 | Dairy | Flavored Yogurt | 103 | 2452 |
2022 | Condiment | Salsa | 104 | 1345 |
2022 | Condiment | Soy Sauce | 105 | 3456 |
2022 | Condiment | Ranch | 106 | 1934 |
2022 | Dairy | Egg | 107 | 2950.6 |
2022 | Cleaning Supplies | Mop | 108 | 2745 |
2022 | Cleaning Supplies | Liquid Detergent | 109 | 5548 |
2023 | Dairy | Flavored Yogurt | 103 | 3756 |
2023 | Dairy | Milk | 101 | 3658 |
2023 | Dairy | Plain Yogurt | 102 | 2534.4 |
2023 | Condiment | Soy Sauce | 105 | 0 |
2023 | Condiment | Salsa | 104 | 2455 |
2023 | Condiment | Ranch | 106 | 3456 |
2023 | Dairy | Egg | 107 | 4793 |
2023 | Cleaning Supplies | Liquid Detergent | 109 | 2356 |
2023 | Cleaning Supplies | Mop | 108 | 2669 |
How can I calculate YoY growth rate of each product ID in the above scenario such that my final output looks like below using proc sql? If the sales for current year is 0, I would like its YoY to be "-". Please help me on this.
Category | Product | Product_ID | YoY |
Dairy | Milk | 101 | 67% |
Dairy | Plain Yogurt | 102 | 43% |
Dairy | Flavored Yogurt | 103 | 35% |
Condiment | Salsa | 104 | 45% |
Condiment | Soy Sauce | 105 | - |
Condiment | Ranch | 106 | 44% |
Dairy | Egg | 107 | 38% |
Cleaning Supplies | Mop | 108 | -3% |
Cleaning Supplies | Liquid Detergent | 109 | -135% |
data have;
infile datalines truncover;
input Year Category & $40. Product & $40. Product_ID sales;
datalines;
2022 Dairy Milk 101 1200
2022 Dairy Plain Yogurt 102 1434.5
2022 Dairy Flavored Yogurt 103 2452
2022 Condiment Salsa 104 1345
2022 Condiment Soy Sauce 105 3456
2022 Condiment Ranch 106 1934
2022 Dairy Egg 107 2950.6
2022 Cleaning Supplies Mop 108 2745
2022 Cleaning Supplies Liquid Detergent 109 5548
2023 Dairy Flavored Yogurt 103 3756
2023 Dairy Milk 101 3658
2023 Dairy Plain Yogurt 102 2534.4
2023 Condiment Soy Sauce 105 0
2023 Condiment Salsa 104 2455
2023 Condiment Ranch 106 3456
2023 Dairy Egg 107 4793
2023 Cleaning Supplies Liquid Detergent 109 2356
2023 Cleaning Supplies Mop 108 2669
;
proc sql;
create table want as
select *,divide(sales-(select sales from have
where Category=a.Category and Product=a.Product and Product_ID=a.Product_ID and year=a.year-1)
,sales) as YoY format=percentn8.2
from have as a
where year=2023;
quit;
data want;
set want;
if YoY=.M then YoY=._;
run;
Shouldn't the YoY for milk be 204% reflecting a 204% increase in 2023 compared to 2022 😲?
(3658-1200)/1200
- Cheers -
Something like this, i suppose:
proc sort data=have;
by year product_id;
run;
data want;
merge have(where=(year=2022))
have(where=(year=2023) keep=year product_id sales rename=(sales=sales23));
by product_id;
if sales23>0 then
YoY=(sales23-sales)/sales23;
drop sales: year;
format YoY percent7.0;
run;
This will get the numbers you showed, but like @Oligolas I think the correct calculation would be YoY=(sales23-sales)/sales
data have;
infile datalines truncover;
input Year Category & $40. Product & $40. Product_ID sales;
datalines;
2022 Dairy Milk 101 1200
2022 Dairy Plain Yogurt 102 1434.5
2022 Dairy Flavored Yogurt 103 2452
2022 Condiment Salsa 104 1345
2022 Condiment Soy Sauce 105 3456
2022 Condiment Ranch 106 1934
2022 Dairy Egg 107 2950.6
2022 Cleaning Supplies Mop 108 2745
2022 Cleaning Supplies Liquid Detergent 109 5548
2023 Dairy Flavored Yogurt 103 3756
2023 Dairy Milk 101 3658
2023 Dairy Plain Yogurt 102 2534.4
2023 Condiment Soy Sauce 105 0
2023 Condiment Salsa 104 2455
2023 Condiment Ranch 106 3456
2023 Dairy Egg 107 4793
2023 Cleaning Supplies Liquid Detergent 109 2356
2023 Cleaning Supplies Mop 108 2669
;
proc sql;
create table want as
select *,divide(sales-(select sales from have
where Category=a.Category and Product=a.Product and Product_ID=a.Product_ID and year=a.year-1)
,sales) as YoY format=percentn8.2
from have as a
where year=2023;
quit;
data want;
set want;
if YoY=.M then YoY=._;
run;
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: