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.
... View more