Calculating an Index

[aguilar_john]

Hello,

I want to calculate an Index based on the Herfindahl-Index formula. The problem is that the names of my columns do not have the same prefix and I have many variables (approx. 260) therefore I prefer not to type every variable manually.

The table looks like this:

Year    ReporterISO3    ReporterName    ABW in 1000 ....    ZWE in 1000   Sum                   HI (goal)
2005    QAT                   Qatar                  200                ....    100                  200+....+100     
2006    QAT                   Qatar                  0                    ....     10                    0+....+10
2005    SEN                  Senegal             1000                ....    200                  1000+....+200
2006    SEN                  Senegal             2000                ....     50                    2000+....+50

The .... stands for the other approx. 258 columns.

My goal is to calcualte:

HI=('ABW in 1000'n/Sum)^2+...+('ZWE in 1000'n/Sum)^2

So that I will have one value for year. I already calculated the sum with an array function. The column header have spaces in the name thats why I used ''n.

Thank you very much for your help!

Kind regards

John

[art297]

Show an example have dataset with, say, three actual variables, and also a want dataset based on the example have dataset.

Art, CEO, AnalystFinder.com

[aguilar_john]

Hello,

I tried to upload a test dataset with the SAS table but that did not quite work. Attached you will find an Excel-sheet with 3 of the 260 columns and the corresponding formulas.

Thank you.

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[aguilar_john]

Thank you very much for the advice.
I downloaded the file but since I dont have the SAS UE but only SAS EG I do not understand what to do...
Is there another way how I can provide my sample dataset?

[art297 · Accepted solution]

Here is a way to do what you want. While you said you already calculated the sum variable, I recalculated it below thus it could be removed from the code and replaced with your already calulated sum:

options validvarname=any;
data have;
  informat ReporterName $30.;
  input year reporterISO3 $ ReporterName & key_geo $
   'ABW in 1000 USD'n
   'AFG in 1000 USD Summe'n
   'ZWE in 1000 USD Summen'n;
  cards;
2004 ITA Italy  ITA2004 13276.394 24696.036 14241.438
2005 ITA Italy  ITA2005 13081.68 26697.422 10059.875
2006 ITA Italy  ITA2006 13950.723 16204.154 8806.267
;
data want;
  set have;
  array all(*) 'ABW in 1000 USD'n--'ZWE in 1000 USD Summen'n;
  array work(99999) _temporary_;
  sum=sum(of all(*));
  do i=1 to dim(all);
    work(i)=(all(i)/sum)**2;
  end;
  hi=sum(of work(*));
run;

Art, CEO, AnalystFinder.com

[aguilar_john]

WOW Thank you very much!!!