macro to read data and declare VAR and IMPVAR

[hadesmr]

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

[Tom]

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;

[hadesmr]

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!

[Tom]

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;

;

[hadesmr]

Thank you Tom!

[RobPratt · Accepted solution]

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;

[RobPratt]

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

[hadesmr]

Thank you RobPratt!

[hadesmr]

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;

[RobPratt]

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;

[hadesmr]

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;

[RobPratt]

Add this line before the SOLVE statement:

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

[hadesmr]

great, thank you, as always!