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/240499#M44378 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.
... View more