topic Re: Calculate YoY growth within groups in SAS Programming

Calculate YoY growth within groups

hk24 — Tue, 18 Apr 2023 21:42:20 GMT

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%

Re: Calculate YoY growth within groups

Oligolas — Wed, 19 Apr 2023 07:45:17 GMT

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

(3658-1200)/1200

Re: Calculate YoY growth within groups

s_lassen — Wed, 19 Apr 2023 09:15:27 GMT

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

Re: Calculate YoY growth within groups

Ksharp — Wed, 19 Apr 2023 11:50:58 GMT

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;