Calculating the PRODUCT for a rolling time period

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Calculating the PRODUCT for a rolling time period

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;

 

 


Accepted Solutions
Solution
Monday
Super User
Posts: 10,209

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

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;

?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Respected Advisor
Posts: 2,981

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

PROC EXPAND has a moving product transformation which sounds like exactly what you want. 

http://documentation.sas.com/?cdcId=etscdc&cdcVersion=14.2&docsetId=etsug&docsetTarget=etsug_expand_...

 

If you don't have PROC EXPAND, let us know because I'm pretty sure you can program this up in a data set.

--
Paige Miller
Solution
Monday
Super User
Posts: 10,209

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

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;

?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 19

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to KurtBremser

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!

Super User
Posts: 10,761

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

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;
Occasional Contributor
Posts: 19

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

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
;


Super User
Posts: 10,209

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 19

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to KurtBremser

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?

 

Super User
Posts: 10,209

Re: Calculating the PRODUCT for a rolling time period

[ Edited ]
Posted in reply to MACRO_LOOP

You just start the calculation one year later (set mult_flag at &startyear + 1)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 19

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to KurtBremser

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;
Super User
Posts: 10,209

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to MACRO_LOOP

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            .
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 19

Re: Calculating the PRODUCT for a rolling time period

Posted in reply to KurtBremser

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 180 views
  • 4 likes
  • 4 in conversation