Solved
Regular Learner
Posts: 1

# Calculations Using Individual Observations

I'm hoping to find a general approach for calculations that require individual cells in a data table, though I'll describe the specific problem that motivated the question as it will likely be a better illustration. I also have some commentary on the work so far which I will place below the question. I am using SAS 9.4.

Question

I have a table where the observations (rows) are values for a given year and the columns are numeric economic variables (with 1 additional column for industry type). My aim is to calculate compound growth rates over these columns ( formula ([later value / early value] ^ [1 / # years] - 1) * 100).

What would be the best way to identify two rows to perform this calculation (or is there another approach to obtain this number entirely)

---

Work so far

Right now I've simply considered hand picking the individual rows for the numerator and denominator, then using the number of entries in between to calculate the root, but this doesn't particularily scale well as it will be for 4 separate time periods over at least 26 variables done for two separate data sets. Assuming I were to employ this approach, this solution seems to cover it:

https://communities.sas.com/t5/Base-SAS-Programming/Calculations-with-multiple-observations/m-p/2404...

and my question could be reduced to 'what is the best way to identify the row without manually entering it each time?'

However, my main reason for posting here is that the approach overall does not follow the same logic of any other SAS solution I've encountered and so I have to entertain the possibility that this solution seems convoluted because I'm ignoring a better alternative.

Accepted Solutions
Solution
‎01-10-2017 11:08 AM
Super User
Posts: 13,287

## Re: Calculations Using Individual Observations

Are you attempting to do this calculation within values of your industry code? If so something like this may be what you are looking for if you want to calculate a cumulative to year each year.

```proc sort data=have;
by industrycode year;
run;

data want;
set have;
by industrycode Year;
retain firstval firstyear;
If first.industrycode then do;
firstval = value;
firstyear =year;
end;

growthrate = (value/firstvalue)**(1/(1 + year - firstyear) -1) *100;
run;```

You didn't provide any varaible names so I used generic ones based on your narrative.

It would help for future questions to post some example data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will create data step code based on your data that you can paste into the forum to generate a data set code can be tested against.

If you are only looking at rate between two years then I would expect the first and last years would be appropriate. If there's some reason that isn't so then you need to provide a rule. If that is the case then add:

if last.industrycode;

just before the Run statement in the data step.

All Replies
Solution
‎01-10-2017 11:08 AM
Super User
Posts: 13,287

## Re: Calculations Using Individual Observations

Are you attempting to do this calculation within values of your industry code? If so something like this may be what you are looking for if you want to calculate a cumulative to year each year.

```proc sort data=have;
by industrycode year;
run;

data want;
set have;
by industrycode Year;
retain firstval firstyear;
If first.industrycode then do;
firstval = value;
firstyear =year;
end;

growthrate = (value/firstvalue)**(1/(1 + year - firstyear) -1) *100;
run;```

You didn't provide any varaible names so I used generic ones based on your narrative.

It would help for future questions to post some example data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will create data step code based on your data that you can paste into the forum to generate a data set code can be tested against.

If you are only looking at rate between two years then I would expect the first and last years would be appropriate. If there's some reason that isn't so then you need to provide a rule. If that is the case then add:

if last.industrycode;

just before the Run statement in the data step.

Super User
Posts: 23,226

## Re: Calculations Using Individual Observations

1. Post sample data that illustrates your problem  - as is, if we're familiar with your problem we can help answer but otherwise no.

2. Its highly likely there's a better approach, but we'd have to mock up data to test anything + write the code. Help us help you by at least providing the sample data.

3. When automating a process you need to generalize...

Here's a quick attempt

[Value(year)  /  Value(year-1) ] ^ [1 / # years of data in table] - 1) * 100

This tells me I'm always looking for previous years numbers and I also need the total number of years available. Now I can start writing a query to solve this.

It's usually easier to do this with a table of information though

☑ This topic is solved.