topic Re: Calculate the Product of Variable Based on Date in New SAS User

Calculate the Product of Variable Based on Date

Toni2 — Mon, 21 Jun 2021 09:47:28 GMT

hi,

I am new is SAS

I am trying to replicate a product calculation in SAS but i am not sure how i can do it - see the attached pdf

In short, I need to replicate column C using the formula (formula in the pdf) which calculates the 12 month growth rate

Then i need to populate all column C. For example, cell C16 needs to be =(PRODUCT(B5:B16)-1)*100, C17 to be =(PRODUCT(B6:B17)-1)*100 and so on

Any help will be appreciated 🙂

Re: Calculate the Product of Variable Based on Date

PaigeMiller — Mon, 21 Jun 2021 11:18:34 GMT

Some of us will not (or cannot) download attachments. You can show us (a portion of) your data and formula by including it in your reply. Data should be provided following these instructions. Formula can be included in your reply as a screen capture.

Re: Calculate the Product of Variable Based on Date

Toni2 — Mon, 21 Jun 2021 12:56:40 GMT

Hi,

Thanks for the advises

Below you can see part of my dataset

I want to create a new variable to calculate the 12 month growth in % for the RD12MG based on date

Starting in 31JUL1983 in Excel the formula that I want to use is =((PRODUCT(G12:G23)-1)*100) where G12 = 1.011 and G23 = 1.008

Then, for example, for next dates the formula has to be :

31AUG1983 the calculation has to be =((PRODUCT(G13:G24)-1)*100) where G13 = 1.008 and G24 = 1.003

30SEP1983 the calculation has to be =((PRODUCT(G14:G25)-1)*100) where G14 = 1.013 and G25 = 1.006

Obs Date RD12mG

2	31JUL1982	.
3	31AUG1982	1.011
4	30SEP1982	1.008
5	31OCT1982	1.013
6	30NOV1982	1.012
7	31DEC1982	1.016
8	31JAN1983	1.005
9	28FEB1983	1.012
10	31MAR1983	1.007
11	30APR1983	1.009
12	31MAY1983	1.018
13	30JUN1983	1.013
14	31JUL1983	1.008
15	31AUG1983	1.003
16	30SEP1983	1.006
17	31OCT1983	1.014
18	30NOV1983	1.011
19	31DEC1983	1.016
20	31JAN1984	1.004
21	29FEB1984	1.009
22	31MAR1984	1.012
23	30APR1984	1.013
24	31MAY1984	1.010
25	30JUN1984	1.010

Re: Calculate the Product of Variable Based on Date

PaigeMiller — Mon, 21 Jun 2021 13:14:58 GMT

Since these are months, there are no missing months, you can use the LAG11 function.

data want;
    set have;
    growth=(RD12mG*lag11(RD12mG)-1)*100;
run;

Re: Calculate the Product of Variable Based on Date

Toni2 — Mon, 21 Jun 2021 13:57:41 GMT

thanks. I think that your formula multiples only the first and last value but we need all the values in the interval

I mean for example, for 31JUL1983 the formula has to calculate growth as below :

growth = (1.011 * 1.008 * 1.013 * 1.012 * 1.016 * 1.005 * 1.012 * 1.007 * 1.009 * 1.011 * 1.013 * 1.008) - 1)*100) = 13.3

Any idea?

Re: Calculate the Product of Variable Based on Date

PaigeMiller — Mon, 21 Jun 2021 14:14:07 GMT

A simple modification to the code:

growth=(RD12mG*lag11(RD12mG)*lag10(RD12mG)*lag9(RD12mG)*
/* I'm lazy, you type the rest */
-1)*100;

You can also use PROC EXPAND, which would be less typing and more flexible.

Re: Calculate the Product of Variable Based on Date

Toni2 — Mon, 21 Jun 2021 14:13:53 GMT

yes, you are right, sorry!

thanks for the support 🙂