turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Calculating Market Potential

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 07:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 09:10 AM

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

All Replies

Solution

04-27-2013
09:10 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 09:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 12:23 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 03:53 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 09:41 PM

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 .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 10:00 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 10:22 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 10:47 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2013 11:01 PM

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

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

Ksharp

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-28-2013 12:42 AM

It worked

Thank you much!! I really appreciate it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 12:21 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 01:51 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-28-2013 01:42 AM

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