topic Re: Calculating Market Potential in SAS Procedures

Calculating Market Potential

sharmas — Sat, 27 Apr 2013 11:54:36 GMT

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.

Re: Calculating Market Potential

Ksharp — Sat, 27 Apr 2013 13:10:07 GMT

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

Re: Calculating Market Potential

sharmas — Sat, 27 Apr 2013 16:23:34 GMT

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.

Re: Calculating Market Potential

sharmas — Sat, 27 Apr 2013 19:53:06 GMT

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.

Re: Calculating Market Potential

Ksharp — Sun, 28 Apr 2013 01:41:05 GMT

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 .

Re: Calculating Market Potential

sharmas — Sun, 28 Apr 2013 02:00:21 GMT

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.

Re: Calculating Market Potential

Ksharp — Sun, 28 Apr 2013 02:22:49 GMT

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

Re: Calculating Market Potential

sharmas — Sun, 28 Apr 2013 02:47:15 GMT

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.

Re: Calculating Market Potential

Ksharp — Sun, 28 Apr 2013 03:01:48 GMT

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

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

Ksharp

Re: Calculating Market Potential

sharmas — Sun, 28 Apr 2013 04:42:44 GMT

It worked

Thank you much!! I really appreciate it.

Re: Calculating Market Potential

Haikuo — Sun, 28 Apr 2013 05:42:57 GMT

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;

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

Re: Calculating Market Potential

sharmas — Mon, 29 Apr 2013 16:21:13 GMT

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.

Re: Calculating Market Potential

sharmas — Mon, 29 Apr 2013 17:51:09 GMT

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.