SAS Optimization, and SAS Simulation Studio

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

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
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2012 04:46 PM

Hi guys,

i have a original dataset "M" like this:

data M;

input week dep m1 m2 m3;

cards;

1 100 12 89 6

2 200 61 34 77

3 300 71 69 55

4 400 18 10 40

5 500 18 14 66

;

run;

proc optmodel;

set w;

num weeks{w};

num dep{w};

num m1{w};

num m2{w};

num m3{w};

read data M into w = [_n_] dep m1 m2 m3;

var wt1{w} >= 0 <= 1 init 1;

var wt2{w} >= 0 <= 1 init 1;

var wt3{w} >= 0 <= 1 init 1;

impvar sp1 {i in w} = m1* * wt3 ;*

impvar sp2 {i in w} = m2 * wt3;

impvar sp3 {i in w} = m3 * wt3;

print sp1 sp2 sp3;

quit;

what i am trying to do here within Proc Optmodel is:

1. read m1 m2 and m3 and dep from dataset M;

2. declare VAR: wt1 wt2 and wt3;

3. decalre IMPVAR: sp1 (m1*wt1) sp2 (m2*wt2) and sp3 (m3*wt3);

i know how to do it in a slow way as above. However, in the real dataset i have 100 variables (m1, m2, ..m100) and thus will have 100 wt variables (VAR) and sp variables (impvar). Is there any macro or faster/smarter way to achieve the bove 3 steps?

Thanks a lot!

week | dep | m1 | m2 | m3 | wt1 | wt2 | wt3 | sp1 | sp2 | sp3 |

1 | 100 | 12 | 89 | 6 | 1 | 2 | 3 | 12 | 178 | 18 |

2 | 200 | 61 | 34 | 77 | 1 | 2 | 3 | 61 | 68 | 231 |

3 | 300 | 71 | 69 | 55 | 1 | 2 | 3 | 71 | 138 | 165 |

4 | 400 | 18 | 10 | 40 | 1 | 2 | 3 | 18 | 20 | 120 |

5 | 500 | 18 | 14 | 66 | 1 | 2 | 3 | 18 | 28 | 198 |

Accepted Solutions

Solution

06-09-2012
10:53 AM

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

06-09-2012 10:53 AM

Here's an alternative approach that uses fewer PROC OPTMODEL statements:

%let n = 3;

proc optmodel;

num n = &n;

set w;

num weeks{w};

num dep{w};

num m{w,1..n};

read data M into w = [_n_] dep {j in 1..n} <m[_N_,j]=col('m'||j)>;

var wt {w,1..n} >= 0 <= 1 init 1;

impvar sp {i in w, j in 1..n} = m[i,j] * wt[i,j];

print sp;

quit;

All Replies

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

Posted in reply to hadesmr

06-08-2012 06:39 PM

If you really have such a simple variable naming convention then simple iterative %DO loops should work fine.

%macro doit(N);

proc optmodel;

set w;

num weeks{w};

num dep{w};

%do j=1 %to &n;

num m&j{w};

%end;

read data M into w = [_n_] dep

%do j=1 %to &n; m&j %end;

;

%do j=1 %to &n;

var wt&j{w} >= 0 <= 1 init 1;

%end;

%do j=1 %to &n;

impvar sp&j {i in w} = m&j* * wt&n ;*

%end;

%do j=1 %to &n; sp&j %end;

;

quit;

%mend doit;

%doit(3);

MPRINT(DOIT): proc optmodel;

MPRINT(DOIT): set w;

MPRINT(DOIT): num weeks{w};

MPRINT(DOIT): num dep{w};

MPRINT(DOIT): num m1{w};

MPRINT(DOIT): num m2{w};

MPRINT(DOIT): num m3{w};

MPRINT(DOIT): read data M into w = [_n_] dep m1 m2 m3 ;

MPRINT(DOIT): var wt1{w} >= 0 <= 1 init 1;

MPRINT(DOIT): var wt2{w} >= 0 <= 1 init 1;

MPRINT(DOIT): var wt3{w} >= 0 <= 1 init 1;

MPRINT(DOIT): impvar sp1 {i in w} = m1* * wt3 ;*

MPRINT(DOIT): impvar sp2 {i in w} = m2* * wt3 ;*

MPRINT(DOIT): impvar sp3 {i in w} = m3* * wt3 ;*

MPRINT(DOIT): print sp1 sp2 sp3 ;

MPRINT(DOIT): quit;

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

06-08-2012 11:21 PM

thanks Tom,

one more question, what if i need to create two more impvar in the code, such that

impvar wt_sum {i in w} = wt1* + wt2 + wt3;*

impvar sp_sum {i in w} = sp1* + sp2 + sp3;*

any macro to do that? thanks!

by the way, the original variable names are not that simple but i have a macro to change all the variables in order to be macro-friendly.

thanks a lot and have a good weekend!

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

Posted in reply to hadesmr

06-09-2012 10:00 AM

You could create those statements with these loops.

impvar wt_sum {i in w} =

%do j=1 %to &n; wt&j* %if &j<&n %then + ; %end;*

;

impvar sp_sum {i in w} =

%do j=1 %to &n; sp&j* %if &j<&n %then + ; %end;*

;

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

06-11-2012 09:50 AM

Thank you Tom!

Solution

06-09-2012
10:53 AM

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

06-09-2012 10:53 AM

Here's an alternative approach that uses fewer PROC OPTMODEL statements:

%let n = 3;

proc optmodel;

num n = &n;

set w;

num weeks{w};

num dep{w};

num m{w,1..n};

read data M into w = [_n_] dep {j in 1..n} <m[_N_,j]=col('m'||j)>;

var wt {w,1..n} >= 0 <= 1 init 1;

impvar sp {i in w, j in 1..n} = m[i,j] * wt[i,j];

print sp;

quit;

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

Posted in reply to RobPratt

06-09-2012 10:59 AM

And here's how you can do the sums if you declare wt and sp as in my earlier reply:

impvar wt_sum {i in w} = sum {j in 1..n} wt[i,j];

impvar sp_sum {i in w} = sum {j in 1..n} sp[i,j];

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

Posted in reply to RobPratt

06-11-2012 09:50 AM

Thank you RobPratt!

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

Posted in reply to RobPratt

06-11-2012 12:08 PM

Hey RobPratt,

A follow up question. Below is the code based on your advice. It works fine but i am wondering if there is a way to simply VAR wt a little bit highlighted in RED.

what i am trying to do here is to create a weight factor (VAR wt) with 2 constrains: 1), sum of wt1, wt2 and wt3 equals to 1; 2). weighting factors are same across all the weeks.

I feel like i don't need "w" in wt {w, 1..n} but don't know how to make the change.

by the way, if i can't make the above change, how to create a dataset only for wt {w, 1..n} in week1?

Thanks again for your help!

------------------------------------------------------------------------------

data M;

input week dep m1 m2 m3;

cards;

1 100 12 89 6

2 200 61 34 77

3 300 71 69 55

4 400 18 10 40

5 500 18 14 66

;

run;

%let n = 3;

proc optmodel;

num n = &n;

set w;

num weeks{w};

num dep{w};

num m{w,1..n};

read data M into w = [_N_] dep {j in 1..n} <m[_N_,j]=col('m'||j)>;

var wt {w,1..n} >= 0 <= 1 init 1;

impvar prdt {i in w, j in 1..n} = m[i,j] * wt[i,j];

impvar swt {i in w} = sum {j in 1..n} wt[i,j];

impvar sp {i in w} = sum {j in 1..n} prdt[i,j];

num wks;

wks = card (w);

impvar correlation = (wks*sum{i in w}(sp**dep ) - (sum{i in w}(sp)) * (sum{i in w}(dep)))*

/ (sqrt(wks*sum{i in w}(sp^2) - (sum{i in w}(sp))^2) * sqrt(wks*sum{i in w}(dep^2)

- (sum{i in w}(dep))^2));

min mincorrelation = correlation;

con weighting {i in w, j in 1..n}: wt[i,j] = if i = 1 then wt[i,j] else wt[i-1,j];

con swt [1] = 1;

solve WITH NLP / tech=ActiveSet;

print correlation percentn10.2 wt prdt swt sp dep;

create data work.sumproduct from * = (w) sp;quit;*

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

Posted in reply to hadesmr

06-11-2012 11:29 PM

Yes, remove w from wt declaration:

var wt {1..n} >= 0 <= 1 init 1;

Replace wt[i,j] with wt

Also, omit weighting constraint.

Finally:

con swt_con: swt = 1;

Or you can omit impvar swt and do this:

con swt_con: sum {j in 1..n} wt

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

Posted in reply to RobPratt

06-29-2012 12:24 PM

Hi RobPratt,

Running a problem when maximizing the correlation. As you can see below, i intended to make m3 all ZERO across all 5 observations (it also happens in the real dataset). When i run solver in Excel, it maximizes the correlation between the "dep" and "sp" by giving weighting values to wt1 and wt2 and **ZERO** to wt3 (because m3 is all zero) with the constrain that wt1+wt2+wt3 = 1. The below code (with your help) replicates the same process but it gives wt3 value (instead of zero). The results show wt1=.656, wt2=0 and wt3=.344. Basically, what i want to do is if all the obs in any given m

Can you please take a look and help me solve this problem? Thanks!

--------------------------------------------------------------------------------------------------------------

data M;

input week dep m1 m2 m3;

cards;

1 100 12 89 0

2 200 61 34 0

3 300 71 69 0

4 400 18 10 0

5 500 18 14 0

;

run;

%let n = 3;

proc optmodel;

num n = &n;

set w;

num weeks{w};

num dep{w};

num m{w,1..n};

read data M into w = [_N_] dep {j in 1..n} <m[_N_,j]=col('m'||j)>;

var wt {1..n} >= 0 <= 1 init 1;

impvar prdt {i in w, j in 1..n} = m[i,j] * wt

impvar sp {i in w} = sum {j in 1..n} prdt[i,j];

num wks;

wks = card (w);

impvar correlation = (wks*sum{i in w}(sp**dep ) - (sum{i in w}(sp)) * (sum{i in w}(dep)))*

/ (sqrt(wks*sum{i in w}(sp^2) - (sum{i in w}(sp))^2) * sqrt(wks*sum{i in w}(dep^2)

- (sum{i in w}(dep))^2));

max maxcorrelation = correlation;

con swt_con: sum {j in 1..n} wt

solve WITH NLP / tech=ActiveSet;

print correlation percentn10.2 wt prdt sp dep;

create data work.sumproduct from [week] = (w) sp;

create data work.weighting from [variable] = {1..n} wt;

quit;

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

Posted in reply to hadesmr

06-29-2012 01:46 PM

Add this line before the SOLVE statement:

for {j in 1..n: and {i in w} (m[i,j] = 0)} fix wt

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

Posted in reply to RobPratt

06-29-2012 02:57 PM

great, thank you, as always!