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 |
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;
%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)??
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;
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;
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]);
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.