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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

2 REPLIES 2
ballardw
Super User

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.

Reeza
Super User

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 😉

 

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
  • 2 replies
  • 742 views
  • 1 like
  • 3 in conversation