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.
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
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
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.
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.
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 .
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.
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
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.
OH. You can use divide() to avoid these error message.
_gdp/x{i}; <=> divide(_gdp,x{i});
Ksharp
It worked
Thank you much!! I really appreciate it.
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.
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: