topic Re: Iterative Excel Solver in SAS (By changing variable cells) in SAS Programming

Iterative Excel Solver in SAS (By changing variable cells)

Baelin10 — Tue, 19 Mar 2019 20:25:02 GMT

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%
2	7	0.285714	8.22%	28.57%
3	7	0.285714	8.22%	28.57%
4	800	0.375	34.21%	37.50%
5	800	0.375	34.21%	37.50%
6	744	0.387097	35.28%	38.71%
6	744	0.387097	35.28%	38.71%
7	2	0.5	9.45%	50.00%
8	2	0.5	9.45%	50.00%

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

Re: Iterative Excel Solver in SAS (By changing variable cells)

PGStats — Tue, 19 Mar 2019 21:33:00 GMT

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.

Re: Iterative Excel Solver in SAS (By changing variable cells)

ChrisNZ — Tue, 19 Mar 2019 23:25:43 GMT

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

Re: Iterative Excel Solver in SAS (By changing variable cells)

Baelin10 — Wed, 20 Mar 2019 16:59:47 GMT

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

Best regards,

Ricardo Soares