Quartz | Level 8

## adjust variables to match totals

Hi,

I would like to adjust the two variables y1 and y2 of dataset have_det so that the total over idgroup is equal to the values in have tot.

I would like to do it with proc optmodel.

the constrains are:
y1sol >=0 ,y2sol >=0
sum(i in idgroup) y1sol=y1tot
sum(i in idgroup) y2sol=y2tot

and the objective is
min sum(i in idgroup) (y1-y1sol)*2
min sum(i in idgroup) (y2-y2sol)*2

basically they are two independent problems: one for y1 and one for y2.

I would appreciate very much for the code.

I would also appreciate any suggestions on the objective function so that the solution is one more similar to the one obtained by scaling the variable(s) to the total(s), that is a raking procedure.

C

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: adjust variables to match totals

I think the following does what you want, with a separate QP solve for each of the two problems.

``````proc optmodel;
set PROBS = 1..2;

set IDS;
num y {PROBS, IDS};
str idgroup {IDS};
read data have_det into IDS=[id] {p in PROBS} <y[p,id]=col('y'||p)> idgroup;

set <str> GROUPS;
num ytot {PROBS, GROUPS};
read data have_tot into GROUPS=[g=idgroup] {p in PROBS} <ytot[p,g]=col('y'||p||'tot')>;

num pThis;

/* declare optimization model for one problem at a time */
var yvar {IDS} >= 0;

con SumPerGroup {g in GROUPS}:
sum {i in IDS: idgroup[i] = g} yvar[i] = ytot[pThis,g];

min Objective = sum {i in IDS} (y[pThis,i] - yvar[i])^2;

num ysol {PROBS, IDS};

/* call solver once for each problem */
do pThis = PROBS;
put pThis=;
solve;
for {i in IDS} ysol[pThis,i] = yvar[i];
end;

/* create SAS output data set */
create data want from [id]
{p in PROBS} <col('y'||p)=y[p,id]>
idgroup
{p in PROBS} <col('y'||p||'sol')=ysol[p,id]>;
quit;``````
7 REPLIES 7
Quartz | Level 8

## Re: adjust variables to match totals

Hi,
please, is there anyone that can help on this?
thank you again for all the support
Opal | Level 21

## Re: adjust variables to match totals

I don't have the subject matter expertise to address your question but I can tell from reading what you provide that your question is certainly "underspecified".

And last but not least: Provide the fully working SAS code that creates the sample "Have" data, provide the desired result and how you want to get there. This again is "helping your helpers" so they can spend the time on the bits you actually need help with.

Quartz | Level 8

## Re: adjust variables to match totals

Thanks Patrick for the answer and the time spent in addressing the problems you saw in my message.
I did not share the code with my attempts because at the moment is a total mess. I tried to adapt something I found online, but there is more than one thing that I do not understand well.
As for the Want dataset I did not post it because I could not solve the problem to get there. As for the code to get the Have datasets it does not seem relevant to me since they are just subsets of bigger data I am working on.
I am just asking for help in this forum. I understand that everybody is very busy and not receiving an answer is a possible outcome for a question. However if you or anybody else felt I am "just fishing for free work" I apologize for this,
I take the chance to thank you and all the people that take the time to help me and anybody else that asks questions.
Super User

## Re: adjust variables to match totals

Sample data does not need to be the real data.  Make an example that is small enough that you can calculate the result by hand so that you can demonstrate what you are trying to do.

Quartz | Level 8

## Re: adjust variables to match totals

The want dataset should be similar to the one obtained as follows:

proc sql;
create table want(drop=y1totdet y2totdet) as select a.*,
y1*(y1tot/y1totdet) as y1sol,
y2*(y2tot/y2totdet) as y2sol
from (select *,sum(y1) as y1totdet,
sum(y2) as y2totdet from have_det group by idgroup) as a
left join
have_tot as b
on a.idgroup=b.idgroup
order by id;
quit;

In this case the scaling obtained via a simple sql is enough. in fact the sum over idgroup are equal to the ones in have_tot dataset:
proc means data=want sum;
class idgroup;
var y1sol y2sol;
run;

but in other cases, due to rounding the totals do not match perfectly.

moreover there are cases when, for instance, all y1 in have_det dataset are 0 and cannot be scaled to an y1tot >0.

that's why I would like to solve the problem via an optimization procedure.

third, but only optional, it would be a plus to have the solutions to be integer numbers.

Probably I have to run the scaling first and then the optmodel procedure with the problem defined on the on the scaled variables.

However my main problem here is how to write the optmodel code to work across records within idgroups. That is what I am unable to do.

Hope this is clearer.

In any case, thank you again.

SAS Super FREQ

## Re: adjust variables to match totals

I think the following does what you want, with a separate QP solve for each of the two problems.

``````proc optmodel;
set PROBS = 1..2;

set IDS;
num y {PROBS, IDS};
str idgroup {IDS};
read data have_det into IDS=[id] {p in PROBS} <y[p,id]=col('y'||p)> idgroup;

set <str> GROUPS;
num ytot {PROBS, GROUPS};
read data have_tot into GROUPS=[g=idgroup] {p in PROBS} <ytot[p,g]=col('y'||p||'tot')>;

num pThis;

/* declare optimization model for one problem at a time */
var yvar {IDS} >= 0;

con SumPerGroup {g in GROUPS}:
sum {i in IDS: idgroup[i] = g} yvar[i] = ytot[pThis,g];

min Objective = sum {i in IDS} (y[pThis,i] - yvar[i])^2;

num ysol {PROBS, IDS};

/* call solver once for each problem */
do pThis = PROBS;
put pThis=;
solve;
for {i in IDS} ysol[pThis,i] = yvar[i];
end;

/* create SAS output data set */
create data want from [id]
{p in PROBS} <col('y'||p)=y[p,id]>
idgroup
{p in PROBS} <col('y'||p||'sol')=ysol[p,id]>;
quit;``````
Quartz | Level 8

## Re: adjust variables to match totals

Thank you so much Rob. It will help me a lot to learn the procedure by studying your code.
Discussion stats
• 7 replies
• 689 views
• 1 like
• 4 in conversation