Calcite | Level 5

## 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!

 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
SAS Super FREQ

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

12 REPLIES 12
Super User

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

Calcite | Level 5

## 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

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

;

Calcite | Level 5

Thank you Tom!

SAS Super FREQ

## 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 Super FREQ

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

Calcite | Level 5

## Re: macro to read data and declare VAR and IMPVAR

Thank you RobPratt!

Calcite | Level 5

## 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?

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

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 Super FREQ

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

Calcite | Level 5

## 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 Super FREQ

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

Calcite | Level 5

## Re: macro to read data and declare VAR and IMPVAR

great, thank you, as always!

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