calculate the values as per the given statement by changing a given value

[Decay2020]

Hi all,

I have 3 inputs, one data "Have" and two values in macro variable "value1" and "value2". I am interested in getting the data "want" as per the condition given below. Thanks in advance.

data have;
infile cards delimiter=",";
input col1 col2 col3;
cards;
1, 0.01, 100

2, 0.02, 90

3, 0.03, 80

4, 0.04, 70

5, 0.02, 60

6, 0.06, 50

7, 0.07, 40

;

run;

%let value1 = -4.608753929;

%let value2 = 0.28179071;

now, we want to calculate col4 as value1 + (value2*col1)

and col5 as EXP(col4)/(1+EXP(col4)) such that

sumproduct(col5, col3)/sum(col3) = sumproduct(col2,col3)/sum(col3) by changing the value1 and keeping the value2 same.

After optimization the "value1" should be -4.56266639 (computed in excel) and col4, col5 should be as given in the table below:

Data Want

col1	col2	col3	col4	col5
1	0.01	100	-4.28087568	0.013641871
2	0.02	90	-3.99908497	0.018002379
3	0.03	80	-3.71729426	0.023723163
4	0.04	70	-3.43550355	0.031204128
5	0.02	60	-3.15371284	0.040945231
6	0.06	50	-2.87192213	0.053559135
7	0.07	40	-2.59013142	0.069776252

[blueskyxyz]

%let value1 = -4.608753929;
%let value2 = 0.28179071;

data want;
	retain sumproduct1 sumproduct2 sum_col3 0;
	set have;
	col4 =&value1. + (&value2.*col1);
	col5=EXP(col4)/(1+EXP(col4));
    sumproduct1=sumproduct1+col5*col3;  col5_col3=col5*col3;
    sumproduct2=sumproduct2+col2*col3;  col2_col3=col2*col3;
	sum_col3=sum_col3+col3;

	sumproduct1_=sumproduct1/sum_col3; 
	sumproduct2_=sumproduct2/sum_col3; 
	xx=col4-col5;
run;

sumproduct(col5, col3)/sum(col3) = sumproduct(col2,col3)/sum(col3)??

[Decay2020]

i need to change the value1 from -4.608753929 such that sum product(col5 and col3)/ sum(col3) = 0.030612

0.030612 is sumproduct(col2,col3)/sum(col3) which is computed in excel.

[blueskyxyz]

proc sql;
    create table sumproduct as
    select -4.608753929 as value1
			,0.28179071 as value2
			,col1,col2,col3
			, calculated value1 + (calculated value2 *col1) as col4
			, EXP( calculated col4)/(1+EXP(calculated col4))as col5
			, sum(calculated col5*col3)/sum(col3)  as sum1, sum(col2*col3)/sum(col3)  as sum2 
    from have;
quit;

/*need a do-loop for value1?*/
data sump;
	set sumproduct;
	if sum1=sum2 then value1=-4.56266639;
run;

[Decay2020]

No, we don't have value1 = -4.56266639.
we need to optimize the value1 starting from -4.608753929 such that sumproduct(col5, col3)/sum(col3) = 0.030612

[RobPratt · Accepted solution]

The following does what you want:

proc optmodel;
   set OBS;
   num col1 {OBS};
   num col2 {OBS};
   num col3 {OBS};
   read data have into OBS=[_N_] col1 col2 col3;

   var value1 init &value1;
   num value2 = &value2;
   impvar col4 {i in OBS} = value1 + value2 * col1[i];
   impvar col5 {i in OBS} = EXP(col4[i]) / (1 + EXP(col4[i]));
   con SumProduct:
      sum {i in OBS} col5[i] * col3[i] = sum {i in OBS} col2[i] * col3[i];

   solve noobj;
   print value1;
   print col1 col2 col3 col4 col5;
   create data want(drop=i) from [i] col1 col2 col3 col4 col5;
quit;

value1
-4.5621

[1]	col1	col2	col3	col4	col5
1	1	0.01	100	-4.2803	0.013649
2	2	0.02	90	-3.9985	0.018012
3	3	0.03	80	-3.7168	0.023736
4	4	0.04	70	-3.4350	0.031220
5	5	0.02	60	-3.1532	0.040966
6	6	0.06	50	-2.8714	0.053586
7	7	0.07	40	-2.5896	0.069811

[Decay2020]

Thank you @RobPratt.. this solution works perfectly. But can we update the constraint:
sum {i in OBS} col5[i] * col3[i] = sum {i in OBS} col2[i] * col3[i];
into :
sumproduct(col5, col3)/sum(col3) = sumproduct(col2,col3)/sum(col3) somehow?
as we need to divide the sumproduct with sum of col3.
I understand it wont impact the result as it is cancelling out, but still needed.

[RobPratt]

   con SumProduct:
      (sum {i in OBS} col5[i] * col3[i]) / (sum {i in OBS} col3[i]) 
    = (sum {i in OBS} col2[i] * col3[i]) / (sum {i in OBS} col3[i]);

[blueskyxyz]

proc optmodel, a new proc step for me, thanks @RobPratt, sas/or never be used in the pharmaceutical industry