Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-06-2022 06:09 AM
(688 views)

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.

thank you in advance

C

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

please, is there anyone that can help on this?

thank you again for all the support

please, is there anyone that can help on this?

thank you again for all the support

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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".

If you're asking for support then you need to help your helpers by trying to formulate as clearly as possible what you have and what you need. It's normally also really appreciated if you already share some of your code (whether already fully working or not) as this demonstrates a) the level of your SAS skills so people can gear their answers toward it and b) that you've already spent time solving the problem and are not just fishing for free work from others.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you so much Rob. It will help me a lot to learn the procedure by studying your code.

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.