BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MACRO_LOOP
Obsidian | Level 7

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
13 REPLIES 13
PaigeMiller
Diamond | Level 26

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
Kurt_Bremser
Super User

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;

?

MACRO_LOOP
Obsidian | Level 7

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!

Ksharp
Super User

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;
MACRO_LOOP
Obsidian | Level 7

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
;


Kurt_Bremser
Super User

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;
MACRO_LOOP
Obsidian | Level 7

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?

 

MACRO_LOOP
Obsidian | Level 7

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;
Kurt_Bremser
Super User

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            .
MACRO_LOOP
Obsidian | Level 7

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

MACRO_LOOP
Obsidian | Level 7

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;
MACRO_LOOP
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1438 views
  • 4 likes
  • 4 in conversation