Calculated columns - compare to reference period

[Chris_LK_87]

I have a dataset that is sorted in years and months. Se data have:

data have;
;
input Year$ month$ sales;
datalines;
2017 Jan 10
2017 Feb 20
2018 Jan 20
2018 Feb 25
2019 Jan 15
2019 Feb 30
2020 Jan 50
2020 Feb 40
2021 Jan 20
2021 Feb 35

;
run;

I would like to compare against a reference period. The reference period is the average of the years 2017, 2018 and 2019 sorted for each month. The reference period is calculated like this:  (2017+2018+2019)/3 for each month. I would like to compare the years 2020 and 2021 to reference period. The comparison should have difference absolute number

This is what I want my table to look like: 

data want;

Year$ Month diff_num;

 2020 jan 35 

 2020 feb 15

 2021 jan 5

 2021 feb 10 

[FreelanceReinh · Accepted solution]

Hello @Chris_LK_87,

Try this:

proc sql;
create table want as
select a.year, a.month, abs(sales-refsales) as diff_num from
  have(where=(year>='2020')) a
  left join
  (select month, avg(sales) as refsales
   from have(where=('2017'<=year<='2019'))
   group by month) b
  on a.month=b.month
order by input(cats(month,year),monyy7.);
quit;