Iterative Excel Solver in SAS (By changing variable cells)

[Baelin10]

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

[PGStats · Accepted solution]

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.

[Baelin10]

Thank you very very much, you solved a big issue here. Worked like a charm! 

Best regards,

Ricardo Soares

[ChrisNZ]

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