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
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;
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;
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 25. 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.