Operations Research topics: SAS/OR,
SAS Optimization, and SAS Simulation Studio

macro to read data and declare VAR and IMPVAR

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

macro to read data and declare VAR and IMPVAR

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!

weekdepm1m2m3wt1wt2wt3sp1sp2sp3
1100128961231217818
22006134771236168231
330071695512371138165
44001810401231820120
55001814661231828198

Accepted Solutions
Solution
‎06-09-2012 10:53 AM
SAS Employee
Posts: 447

Re: macro to read data and declare VAR and IMPVAR

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,687

Re: macro to read data and declare VAR and IMPVAR

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;

print

%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;

Contributor
Posts: 21

Re: macro to read data and declare VAR and IMPVAR

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!

Super User
Super User
Posts: 6,687

Re: macro to read data and declare VAR and IMPVAR

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;

;


Contributor
Posts: 21

Re: macro to read data and declare VAR and IMPVAR

Thank you Tom!

Solution
‎06-09-2012 10:53 AM
SAS Employee
Posts: 447

Re: macro to read data and declare VAR and IMPVAR

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;

SAS Employee
Posts: 447

Re: macro to read data and declare VAR and IMPVAR

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];

Contributor
Posts: 21

Re: macro to read data and declare VAR and IMPVAR

Thank you RobPratt!

Contributor
Posts: 21

Re: macro to read data and declare VAR and IMPVAR

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;

SAS Employee
Posts: 447

Re: macro to read data and declare VAR and IMPVAR

Yes, remove w from wt declaration:

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

Replace wt[i,j] with wt everywhere.

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 = 1;

Contributor
Posts: 21

Re: macro to read data and declare VAR and IMPVAR

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 = 0 the wt must be 0.

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 = 1;
   
    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;

SAS Employee
Posts: 447

Re: macro to read data and declare VAR and IMPVAR

Add this line before the SOLVE statement:

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

Contributor
Posts: 21

Re: macro to read data and declare VAR and IMPVAR

great, thank you, as always!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 652 views
  • 6 likes
  • 3 in conversation