Thank you very much for taking time to read this post.
I have a column "ICBSUN" with industry classification codes and a separate column "EV/EBITDA" with an EBITDA multiple.
Now I would like to create a new column "Peer Multiple" that takes the Harmonic mean of the "EV/EBITDA" Coulomn of all Companies with the ICBSUC Code 452010 except the one company of the row itself.
So for example to get the first Value in the "Peer Multiple" Column for the table below it should take the Harmonic mean of the rows 2-12 from column "EV/EBITDA".
However the amount of companies companies in each industry varies and may not be sorted like this example.
Can anyone help? Thank you very much! 🙂
ISIN | NAME | ICBSUN | ICBSUC | EV | EBITDA | EV/EBITDA | Peer Multiple |
US7163821066 | PETMED EXPRESS | Personal Care, Drug and Grocery Stores | 452010 | 479179 | 55915 | 8,569775552 | |
US58155Q1031 | MCKESSON | Personal Care, Drug and Grocery Stores | 452010 | 22314940 | 1646000 | 13,55707169 | |
US1266501006 | CVS HEALTH | Personal Care, Drug and Grocery Stores | 452010 | 96725580 | 6743000 | 14,34459143 | |
US03073E1055 | AMERISOURCEBERGEN | Personal Care, Drug and Grocery Stores | 452010 | 15863410 | 1909030 | 8,309670356 | |
US9314271084 | WALGREENS BOOTS ALLIANCE | Personal Care, Drug and Grocery Stores | 452010 | 65059276 | 8293000 | 7,845083323 | |
US1475281036 | CASEY'S GENERAL STORES | Personal Care, Drug and Grocery Stores | 452010 | 4725076 | 487930 | 9,683921874 | |
US5010441013 | KROGER | Personal Care, Drug and Grocery Stores | 452010 | 22844120 | 5229000 | 4,368735896 | |
US8718291078 | SYSCO | Personal Care, Drug and Grocery Stores | 452010 | 32623406 | 3047247 | 10,70586205 | |
US9111631035 | UNITED NATURAL FOODS | Personal Care, Drug and Grocery Stores | 452010 | 533852 | 316847 | 1,684888921 | |
US1630861011 | CHEFS' WAREHOUSE | Personal Care, Drug and Grocery Stores | 452010 | 1156331 | 70795 | 16,33351225 | |
US85208M1027 | SPROUTS FARMERS MARKET | Personal Care, Drug and Grocery Stores | 452010 | 2274470 | 334931 | 6,790861401 | |
US8472151005 | SPARTANNASH | Personal Care, Drug and Grocery Stores | 452010 | 517638 | 155371 | 3,331625593 | |
US92240M1080 | VECTOR GROUP | Tobacco | 451030 | 1982857 | 287700 | 6,892099409 | |
US02209S1033 | ALTRIA GROUP | Tobacco | 451030 | 92731859 | 9375000 | 9,891398293 | |
US9134561094 | UNIVERSAL | Tobacco | 451030 | 1349999 | 208009 | 6,490098986 | |
US2186811046 | CORE MARK HOLDING | Tobacco | 451030 | 1226642 | 133100 | 9,2159429 | |
US7181721090 | PHILIP MORRIS INTL. | Tobacco | 451030 | 132391077 | 12515000 | 10,57859185 | |
US0394831020 | ARCHER DANIELS MIDLAND | Food Producers | 451020 | 25816950 | 2847000 | 9,068124341 | |
US4571871023 | INGREDION | Food Producers | 451020 | 6210685 | 949000 | 6,544452055 | |
US1344291091 | CAMPBELL SOUP | Food Producers | 451020 | 9929990 | 885000 | 11,22032768 | |
US2058871029 | CONAGRA BRANDS | Food Producers | 451020 | 8348107 | 1294300 | 6,449901105 | |
US2372661015 | DARLING INGREDIENTS | Food Producers | 451020 | 4598805 | 366367 | 12,55245423 | |
US90328M1071 | USANA HEALTH SCIENCES | Food Producers | 451020 | 1701000 | 208389 | 8,162618948 | |
US3703341046 | GENERAL MILLS | Food Producers | 451020 | 23095314 | 3139800 | 7,355664055 |
So let's start by referring to this example at Wikipedia of the harmonic mean of three numbers.
Here's an outline of how to do this:
You want the sum of the reciprocals of all of the values. You can get this by groups, by first computing the inverse of the ev_ebitda in a SAS data step and them summing these inverses by group using PROC SUMMARY. This will give you the denominator of the entire group harmonic mean, the numerator is of course N for each group. Next, to adjust the harmonic mean by removing one row at a time, again in a SAS data set, you can compute the numerator as N-1 and the denominator as the denominator of the group harmonic mean computed above MINUS the inverse of ev_ebitda for this one row.
Thank you very much @PaigeMiller .
my code now looks like this.
Let's back up. You can't have code EV/EBITDA=EV/EBITDA; because variable names can't have a slash in the name (and even if you could, this statement is meaningless and doesn't do anything). Run the code, get it working, then show us where you get stuck.
UNTESTED CODE, assumes sas_test is sorted by icbsuc
data work.Sas_test;
set work.Sas_test;
inverse=EBITDA/EV;
run;
proc summary data=sas_test nway;
class icbsuc;
var inverse;
output out=sum_inverse sum=sum_inverse n=n;
run;
data want;
merge sas_test sum_inverse;
by icbsuc;
peer_multiple = (n-1) / (sum_inverse - inverse);
run;
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!
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.