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

Showing results for

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-08-2012 04:46 PM
(1500 views)

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 |

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you Tom!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you RobPratt!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

great, thank you, as always!

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.