Hello All
I'm trying to replicate in SAS a procedure I use in Excel. This procedure is basically an iterative solver (through a macro) that puts the variable Delta (OptVar - Ratio) to 0 by changing the values of the AuxVar (starting with null values).
The input data starts like this:
ID Num Ratio AuxVar OptVar Delta
1 | 6 | 0.166667 | 0.00% | 0.00% | -0.1667 |
2 | 7 | 0.285714 | 0.00% | 0.00% | -0.2857 |
3 | 7 | 0.285714 | 0.00% | 0.00% | -0.2857 |
4 | 800 | 0.375 | 0.00% | 0.00% | -0.3750 |
5 | 800 | 0.375 | 0.00% | 0.00% | -0.3750 |
6 | 744 | 0.387097 | 0.00% | 0.00% | -0.3871 |
6 | 744 | 0.387097 | 0.00% | 0.00% | -0.3871 |
7 | 2 | 0.5 | 0.00% | 0.00% | -0.5000 |
8 | 2 | 0.5 | 0.00% | 0.00% | -0.5000 |
- ID, Num and Ratio are inputs;
- AuxVar values are generated by Excel;
- OptVar is given by probit(0.975)*sqrt((AuxVar*(1-AuxVar))/Num)+AuxVar
- Delta is OptVar - Ratio
The solver objective function is Delta, it operates by changing AuxVar values and the constraint is AuxVar greater or equal to 0. It runs for each cell.
After the Solver Macro, the output is:
ID Num Ratio AuxVar OptVar Delta
1 | 6 | 0.166667 | 3.01% | 16.67% | 0.0000 |
2 | 7 | 0.285714 | 8.22% | 28.57% | 0.0000 |
3 | 7 | 0.285714 | 8.22% | 28.57% | 0.0000 |
4 | 800 | 0.375 | 34.21% | 37.50% | 0.0000 |
5 | 800 | 0.375 | 34.21% | 37.50% | 0.0000 |
6 | 744 | 0.387097 | 35.28% | 38.71% | 0.0000 |
6 | 744 | 0.387097 | 35.28% | 38.71% | 0.0000 |
7 | 2 | 0.5 | 9.45% | 50.00% | 0.0000 |
8 | 2 | 0.5 | 9.45% | 50.00% | 0.0000 |
Is there a way to do this in SAS?
I have been trying and searching for a few days, without success.
If anyone could help, it would be very appreciated.
Kind Regards,
Ricardo
Proc FCMP provides a solver that can do this:
proc fcmp outlib=work.fcmp.stuff;
function OptVar(AuxVar, Num);
return ( probit(0.975)*sqrt((AuxVar*(1-AuxVar))/Num) + AuxVar );
endsub;
function AuxVar(Num, ratio);
return ( solve("OptVar", {0.1}, ratio, ., Num) );
endsub;
run;
options cmplib=(work.fcmp);
data have;
input ID Num Ratio;
auxVar = AuxVar(Num, Ratio);
format optVar auxVar percent8.2;
optVar = OptVar(auxVar, Num);
datalines;
1 6 0.166667
2 7 0.285714
3 7 0.285714
4 800 0.375
5 800 0.375
6 744 0.387097
7 2 0.5
;
proc print data=have; run;
Obs ID Num Ratio auxVar optVar 1 1 6 0.16667 3.01% 16.67% 2 2 7 0.28571 8.22% 28.57% 3 3 7 0.28571 8.22% 28.57% 4 4 800 0.37500 34.21% 37.50% 5 5 800 0.37500 34.21% 37.50% 6 6 744 0.38710 35.28% 38.71% 7 7 2 0.50000 9.45% 50.00%
Nore: If this is a power calculation, you might want to look at the Power proc.
Proc FCMP provides a solver that can do this:
proc fcmp outlib=work.fcmp.stuff;
function OptVar(AuxVar, Num);
return ( probit(0.975)*sqrt((AuxVar*(1-AuxVar))/Num) + AuxVar );
endsub;
function AuxVar(Num, ratio);
return ( solve("OptVar", {0.1}, ratio, ., Num) );
endsub;
run;
options cmplib=(work.fcmp);
data have;
input ID Num Ratio;
auxVar = AuxVar(Num, Ratio);
format optVar auxVar percent8.2;
optVar = OptVar(auxVar, Num);
datalines;
1 6 0.166667
2 7 0.285714
3 7 0.285714
4 800 0.375
5 800 0.375
6 744 0.387097
7 2 0.5
;
proc print data=have; run;
Obs ID Num Ratio auxVar optVar 1 1 6 0.16667 3.01% 16.67% 2 2 7 0.28571 8.22% 28.57% 3 3 7 0.28571 8.22% 28.57% 4 4 800 0.37500 34.21% 37.50% 5 5 800 0.37500 34.21% 37.50% 6 6 744 0.38710 35.28% 38.71% 7 7 2 0.50000 9.45% 50.00%
Nore: If this is a power calculation, you might want to look at the Power proc.
Thank you very very much, you solved a big issue here. Worked like a charm!
Best regards,
Ricardo Soares
You can always try the brute-force approach.
data HAVE;
retain RATIO 0.166667 NUM 6 ;
run;
data WANT;
DELTA=9999;
set HAVE;
do I = 0 to 1 by 1e-6;
VAL = probit(0.975)*sqrt((I*(1-I))/NUM)+I;
if . < abs(VAL - RATIO) < DELTA then do;
DELTA = abs(VAL - RATIO) ;
OPTVAR = VAL;
AUXVAR = I;
end;
end;
putlog OPTVAR= percent9.2 AUXVAR= percent9.2;
run;
OPTVAR=16.67% AUXVAR=3.01%
[Edit : I didn't see the previous reply. @PGStats 's solution is smarter. ]
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.