Calculated columns - compare to reference period

Chris_LK_87 — Mon, 05 Jul 2021 12:12:00 GMT

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

Re: Calculated columns - compare to reference period

FreelanceReinh — Mon, 05 Jul 2021 13:01:06 GMT

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;

topic Re: Calculated columns - compare to reference period in SAS Programming

Calculated columns - compare to reference period

Re: Calculated columns - compare to reference period