Solved
Contributor
Posts: 22

# Calculating Market Potential

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.

Accepted Solutions
Solution
‎04-27-2013 09:10 AM
Super User
Posts: 10,787

## Re: Calculating Market Potential

```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

All Replies
Solution
‎04-27-2013 09:10 AM
Super User
Posts: 10,787

## Re: Calculating Market Potential

```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

Contributor
Posts: 22

## Re: Calculating Market Potential

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.

Contributor
Posts: 22

## Re: Calculating Market Potential

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.

Super User
Posts: 10,787

## Re: Calculating Market Potential

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 .

Contributor
Posts: 22

## Re: Calculating Market Potential

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.

Super User
Posts: 10,787

## Re: Calculating Market Potential

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

Contributor
Posts: 22

## Re: Calculating Market Potential

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.

Super User
Posts: 10,787

## Re: Calculating Market Potential

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

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

Ksharp

Contributor
Posts: 22

## Re: Calculating Market Potential

It worked

Thank you much!! I really appreciate it.

Contributor
Posts: 22

## Re: Calculating Market Potential

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.

Contributor
Posts: 22

## Re: Calculating Market Potential

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.

Posts: 3,167

## Re: Calculating Market Potential

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

🔒 This topic is solved and locked.