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. ]
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: