Obsidian | Level 7

## Calculate YoY growth within groups

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%

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate YoY growth within groups

``````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;``````
3 REPLIES 3
Barite | Level 11

## Re: Calculate YoY growth within groups

Shouldn't the YoY for milk be 204% reflecting a 204% increase in 2023 compared to 2022  😲?

(3658-1200)/1200

________________________

- Cheers -

Meteorite | Level 14

## Re: Calculate YoY growth within groups

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`

Super User

## Re: Calculate YoY growth within groups

``````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;``````
Discussion stats
• 3 replies
• 224 views
• 1 like
• 4 in conversation