BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sharmas
Calcite | Level 5

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.

      

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

12 REPLIES 12
Ksharp
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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

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

Ksharp

sharmas
Calcite | Level 5

It worked Smiley Happy

Thank you much!! I really appreciate it.

sharmas
Calcite | Level 5

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
Calcite | Level 5

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1362 views
  • 0 likes
  • 3 in conversation