Hi Everyone,
I am trying to calculate the PRODUCT of a rolling period in SAS.
I have used the solution shown here as a starting point - https://communities.sas.com/t5/Base-SAS-Programming/Calculating-a-rolling-sum-for-the-last-30-days/t...
The code is very close to what I need. The only thing I need to figure out is how to substitute a PRODUCT function in place of the SUM function shown in the example. Try as I might, I can't seem to figure out a solution.
I have seen suggestions that the GEOMEAN function can be used to replicate how the PRODUCT function works in excel. See here https://communities.sas.com/t5/SAS-Procedures/Is-there-any-multiplication-function-in-sas/td-p/50298
If anyone is aware of a method of calculating the PRODUCT for a rolling time period, I would be very grateful for any help!
data ydelser;
input PERSONID$ TOTAL;
datalines;
A 10
A 12
A 14
A 17
B 30
B 30
B 40
B 50
;
Data newdata;
set ydelser;
by PERSONID notsorted;
if first.PERSONID then TOTALsum = 0;
TOTALsum+TOTAL;
run;
Proc Print data = newdata;
run;
Something like
data newdata;
set ydelser;
by personid notsorted;
retain total_product;
if first.personid then total_product = 1;
total_product = total_product * total;
run;
?
PROC EXPAND has a moving product transformation which sounds like exactly what you want.
If you don't have PROC EXPAND, let us know because I'm pretty sure you can program this up in a data set.
Something like
data newdata;
set ydelser;
by personid notsorted;
retain total_product;
if first.personid then total_product = 1;
total_product = total_product * total;
run;
?
Thank You for all of the replies!
They have all been very helpful and I have learned a lot.
Many thanks for taking time to help me on this. Highly appreciated!
You mean this?
proc sql;
create table x as
select *,( select exp(sum(log(air))) from sashelp.air
where date between intnx('month',a.date,-3) and a.date)
as rolling_product
from sashelp.air as a;
quit;
Hi Everyone,
Original poster here.
I have made some progress with the code provided yesterday, but I am still struggling with one part.
The code I am working with is shown below.
What I need to achieve is the following:
In 2016, I would like to find the product of future years (i.e. 2017-2022)
In 2017, I would like to find the product of future years (i.e. 2018-2022).
My data is shown in the program code window further down
The program code that I am currently using to calculate the product is shown below. Does anyone know how I can adapt this code to achieve what I need (i.e. In 2016, I would like to find the product of future years (i.e. 2017-2022)
In 2017, I would like to find the product of future years (i.e. 2018-2022).)
DATA lgd_calculation_preperation_2;
set Calculation1;
by Country;
retain total_product1;
if first.Country then total_product1=1;
if Country="Spain" then output;
total_product1 = total_product1 * calculation;
run;
data YEAR;
input COUNTRY$ YEAR VALUE;
datalines;
SPAIN 2016 10
SPAIN 2017 11
SPAIN 2018 13
SPAIN 2019 12
SPAIN 2020 15
SPAIN 2021 16
SPAIN 2022 11
Germany 2016 12
Germany 2017 18
Germany 2018 21
Germany 2019 13
Germany 2020 12
Germany 2021 12
Germany 2022 13
;
Just start calculating at a given year:
data year;
input COUNTRY$ YEAR VALUE;
datalines;
SPAIN 2016 10
SPAIN 2017 11
SPAIN 2018 13
SPAIN 2019 12
SPAIN 2020 15
SPAIN 2021 16
SPAIN 2022 11
Germany 2016 12
Germany 2017 18
Germany 2018 21
Germany 2019 13
Germany 2020 12
Germany 2021 12
Germany 2022 13
;
run;
proc sort data=year;
by country year;
run;
%let startyear=2017;
data lgd_calculation_preperation_2;
set year;
by Country;
retain total_product1 mult_flag;
if first.Country
then do;
total_product1 = .;
mult_flag = 0;
end;
if year = &startyear
then do;
mult_flag = 1;
total_product1 = 1;
end;
if mult_flag then total_product1 = total_product1 * value;
drop mult_flag;
run;
Hi Kurt,
Thank You for the reply.
The code you provided has been very helpful. I almost have it working.
The problem is that the code shown is working in reverse. I need the code to be forward looking rather than backward looking
For example, for year 2017, I need to find the PRODUCT of the values for years (2018-2022).
Is it possible to amend your code to achieve this?
You just start the calculation one year later (set mult_flag at &startyear + 1)
Hi Kurt,
I can't seem to get this working.
The code I am using is shown below. I have also shown exactly what I need in the table further down.
Do you have any suggestions on what I need to do to get this working.
To confirm, what I need to Find is the product of the values of the subsequent years.
For example, For the year 2017, I need the product of the values in the Years (2018-2022).
data year;
input COUNTRY$ YEAR VALUE;
datalines;
SPAIN 2016 10
SPAIN 2017 11
SPAIN 2018 13
SPAIN 2019 12
SPAIN 2020 15
SPAIN 2021 16
SPAIN 2022 11
Germany 2016 12
Germany 2017 18
Germany 2018 21
Germany 2019 13
Germany 2020 12
Germany 2021 12
Germany 2022 13
;
run;
proc sort data=year;
by country year;
run;
%let startyear=2017;
data lgd_calculation_preperation_2;
set year;
by Country;
retain total_product1 mult_flag;
if first.Country
then do;
total_product1 = .;
mult_flag = 0;
end;
if year = &startyear
then do;
mult_flag =&startyear+ 1;
total_product1 =1;
end;
if mult_flag then total_product1 = total_product1 * value;
drop mult_flag;
run;
data year;
input COUNTRY$ YEAR VALUE total_product1;
datalines;
Germany 2016 12 9199008
Germany 2017 18 511056
Germany 2018 21 24336
Germany 2019 13 1872
Germany 2020 12 156
Germany 2021 12 13
Germany 2022 13
;
run;
Oh, you don't need the product from 2018 to 2022, you need it from 2022 down to 2018!
Sort in reverse, and then apply the basically same logic, with just a little twist when the output happens:
data year;
input COUNTRY$ YEAR VALUE;
datalines;
SPAIN 2016 10
SPAIN 2017 11
SPAIN 2018 13
SPAIN 2019 12
SPAIN 2020 15
SPAIN 2021 16
SPAIN 2022 11
Germany 2016 12
Germany 2017 18
Germany 2018 21
Germany 2019 13
Germany 2020 12
Germany 2021 12
Germany 2022 13
;
run;
proc sort data=year;
by country descending year;
run;
%let startyear = 2017;
data lgd_calculation_preperation_2;
set year;
by Country;
retain total_product1 mult_flag;
if first.Country
then do;
total_product1 = .;
mult_flag = 0;
end;
output; /* that's the kicker */
if year = &startyear + 5 /* and that */
then do;
mult_flag = 1;
total_product1 = 1;
end;
if mult_flag then total_product1 = total_product1 * value;
drop mult_flag;
run;
proc sort data=lgd_calculation_preperation_2;
by country year;
run;
proc print data=lgd_calculation_preperation_2 noobs;
run;
Result:
total_ COUNTRY YEAR VALUE product1 Germany 2016 12 9199008 Germany 2017 18 511056 Germany 2018 21 24336 Germany 2019 13 1872 Germany 2020 12 156 Germany 2021 12 13 Germany 2022 13 . SPAIN 2016 10 4530240 SPAIN 2017 11 411840 SPAIN 2018 13 31680 SPAIN 2019 12 2640 SPAIN 2020 15 176 SPAIN 2021 16 11 SPAIN 2022 11 .
Kurt,
I cannot thank you enough - This has made my day!
The solution you provided has worked a treat.
You have been very patient with this query and the time you have taken to respond is highly appreciated.
Best Wishes
Hi Everyone,
I have a query that is very similar to the previous request.
I have adapted the code to find the SUM of the values, but I now need to adapt the code to find the AVERAGE of the values.
The code that I am using for the SUM of the values is below and works well. Does anyone have suggestions on how I can adapt this code so that it finds the MEAN/AVERAGE of all values?
All suggestions are very much appreciated
data year;
input COUNTRY$ YEAR VALUE;
datalines;
SPAIN 2016 10
SPAIN 2017 11
SPAIN 2018 13
SPAIN 2019 12
SPAIN 2020 15
SPAIN 2021 16
SPAIN 2022 11
Germany 2016 12
Germany 2017 18
Germany 2018 21
Germany 2019 13
Germany 2020 12
Germany 2021 12
Germany 2022 13
;
run;
proc sort data=year;
by country year;
run;
%let startyear=2018;
data lgd_calculation_preperation_2;
set year;
by Country;
retain SUM_VALUES mult_flag ;
if first.Country
then do;
SUM_VALUES = .;
mult_flag = 0;
end;
if year= &startyear
then do;
mult_flag = 1;
SUM_VALUES = 0;
end;
if mult_flag then SUM_VALUES = SUM_VALUES + value;
;
drop mult_flag;
run;
Hi Everyone,
I am trying to find the average of the final 5 years of the VALUE column for each country.
I am working with the below data and the code that I have developed thus far is shown below.
The problem I face is that it is calculating the average of all years, rather than just the last 5.
I have tried to correct this by defining a start year but it doesn't seem to be working.
Does anyone have suggestions on how I can calculate the average of the final 5 values in each by group using the code I have thus far as a starting point.
All help is very much appreciated.
/*DECLARE START YEAR*/
%let startyear = 2018;
data year;
input COUNTRY$ YEAR VALUE;
datalines;
SPAIN 2016 10
SPAIN 2017 11
SPAIN 2018 13
SPAIN 2019 12
SPAIN 2020 15
SPAIN 2021 16
SPAIN 2022 11
Germany 2016 12
Germany 2017 18
Germany 2018 21
Germany 2019 13
Germany 2020 12
Germany 2021 12
Germany 2022 13
;
run;
PROC SORT DATA=year
OUT=Sorted;
BY Country;
RUN;
DATA average;
set Sorted;
by country year;
if (first.country and year = &startyear) then
do;
y = 0;
n = 0;
end;
n + 1;
y + value;
avg = y/n;
run;
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.