Calculating Market Potential

[sharmas]

Hey all,

I am trying to calculate Market Potential and struggling with it. What I have is 49 countries, their GDP and the distances between them (so a 49*49 matrix with 0 on the diagonal, for the distance). The formula to calculate market potential is to sum the distance-weighted GDP, i.e. MarketPotential(germany) = Summation (GDP of all acountries except k)/Distance between them.

Does anyone have any idea how to do this? Your help is much appreciated.

Sample Data:

Country     GDP         Col1      Col2     Col3.............Col49

Germany      5             0          3           1                  2

France         4              1           0          2                 1

Belgium        1              1         2           0                  1

So basically Market potential for Germany = (GDP of france)/Distance between germany and france (3 in this case)  + (GDP of Belgium)/(Distance between Ger and Bel (1 in this case)

Any help is much appreciated. Thanks.

[Ksharp · Accepted solution]

OK. How about this :

data have;
input Country  $   GDP         Col1      Col2     Col3 ;
cards;
Germany      5             0          3           1     
France         4              1           0          2    
Belgium        1              1         2           0   
;
run;
data have;
 set have;
 k=cats('Col',_n_);
run;
data want;
 if _n_ eq 1 then do;
  if 0 then set have(keep=k gdp rename=(gdp=_gdp));
  declare hash ha(dataset:'have(keep=k gdp rename=(gdp=_gdp))');
   ha.definekey('k');
   ha.definedata('_gdp');
   ha.definedone();
 end;
set have;
array x{*} col: ;
sum=0;
do i=1 to dim(x);
 if _n_ ne i then do;
                   k=vname(x{i});
                   ha.find();
                       sum + _gdp/x{i};
                      end;
end;
run;

Ksharp

[sharmas]

Ksharp, You're the man!!! Thank you. The sum gives exactly what I want. Although I don't understand much of what you did. But I'll try and look it up. thanks.

[sharmas]

Hey Ksharp,

I was trying to do what you did. But I ran into a problem. When i run it, it says ERROR: KEY NOT FOUND

And I realize its for ha.find() command. But i'm not sure how to fix the problem. i tried doing something random like rc=ha.(find) but don't know how to correct the program. Appreciate your help once again.

[Ksharp]

So are you sure there are 49 countries, and there are Col1-Col49 in dataset ? and have the same variable name Col1 ......   not col1 or COL1 .

[sharmas]

Hey Ksharp,

I have 49 countries, and there are col1-col49 in the dataset with the same variable name. When i did rc=ha.find(), the program runs, but the sum is zero. if i just do ha.find(), it says ERROR: Key not found.

Can't seem to figure out the problem. Been trying for a while now.

Thanks for your help. I appreciate it.

[Ksharp]

OH. in your origin post ,it is Col, not your said col .they are different for HashTable . Therefore Change it . I am sure you will get what you want.

k=cats('col',_n_);

And try use it.

array x{*} col1-col49 ;

Ksharp

Message was edited by: xia keshan

[sharmas]

Hey,

Sorry I didn't realize that uppercase/lowercase mattered in Hash table. The program ran after that. Thank you.

But ran into another problem. Most of the distances are 0 because they are two far away from each other, so while calculating the sum, many will be divided by 0, and hte program will give an error. Is there a way to correct it?

Sorry, I don't use SAS much, and you've been tremendous help. Thanks again.

[Ksharp]

OH. You can use divide() to avoid these error message.

_gdp/x{i};        <=>     divide(_gdp,x{i});

Ksharp

[sharmas]

It worked

Thank you much!! I really appreciate it.

[sharmas]

Hey Ksharp,

I wanted calculate the weighted gdp of other countries to find the market potential now. So my original sample

Sample Data: ( The col1-49 show the distance between the two countries and is symmetric)

Country     GDP         Col1      Col2     Col3.............Col49

Germany      5             0          3           1                  2

France         4              3           0          2                 1

Belgium        1              1         2           0                  1

Now to calculate the market potential for Germany, I need: MktPot = [(France GDP * France Distance from Germany) + (Belgium GDP * Belgium distance from Germany)] / (Sum of the distances)

                                                                       In this sample data, MktPot Germany = [ (4*3) + (1*1) ] / (3+1)

Can you please help me on how to go about that. I was a bit confused on calculating the row sum as desired. Thanks much again. You're a savior.

[sharmas]

Hey, I figured it out. I just added a new variable  just like sum to calculate to the sum of the rows/columns. It worked. thanks for helping though.

[Haikuo]

Key of your problem is to line up your rows and your columns. You could use two dimension Array, Or you can just use a 2x DOW:

data have;

input Country  $   GDP         Col1      Col2     Col3 ;

cards;

Germany      5             0          3           1    

France         4              1           0          2   

Belgium        1              1         2           0  

;

data want;

do _n_=1 by 1 until (last);

  set have end=last;

  array t(3) _temporary_;

  array c(3) col:;

  do i=1 to 3;

     t(_n_)=gdp;

  end;

end;

do until (last1);

  set have end=last1;

  do i=1 to 3;

     if c(i) ne 0 then  potential+t(i)/c(i);

  end;

  output;

  potential=0;

end;

run;

Haikuo