topic Re: Calculating an Index in SAS Programming

Calculating an Index

aguilar_john — Wed, 19 Jul 2017 16:50:23 GMT

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

Re: Calculating an Index

art297 — Wed, 19 Jul 2017 17:20:52 GMT

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

Re: Calculating an Index

aguilar_john — Wed, 19 Jul 2017 17:33:41 GMT

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.

Re: Calculating an Index

ballardw — Wed, 19 Jul 2017 17:57:45 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Calculating an Index

aguilar_john — Wed, 19 Jul 2017 18:13:43 GMT

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?

Re: Calculating an Index

art297 — Wed, 19 Jul 2017 18:24:54 GMT

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

Re: Calculating an Index

aguilar_john — Wed, 19 Jul 2017 18:31:07 GMT

WOW Thank you very much!!!