Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-21-2021 05:47 AM
(1833 views)

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

yes, you are right, sorry!

thanks for the support 🙂

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!

Upcoming Events

- Montreal SAS User Group (MONSUG) Meeting - Fall | 09-Oct-2024
- Ask the Expert: Wie nutze ich das volle Potenzial von SAS® Intelligent Decisioning? | 10-Oct-2024
- Modernize to SAS® Viya® with HPE GreenLake High-Performance, On-Premises, Pay-As-You-Go Solution | 10-Oct-2024
- SAS Bowl XLIV, Model Cards | 16-Oct-2024
- Ask the Expert: Fostering Trustworthy AI Using a Model Card | 17-Oct-2024
- MSUG Presents: | 17-Oct-2024
- BASUG SAS Blowout (In Person) | 18-Oct-2024

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.