Converting Excel sampling formula to SAS code

[tpt1]

Hello-

I am trying to convert an Excel formula to SAS code.  I do not have experience with statistics, so this is particularly challenging.  The Excel formula assumes a confidence interval of 90%, a precision rate of 5%, and an exception rate of 5%.  These three values will not change.  The only variable is the population size, which I am labeling in the formula as "pop_sz".  The pop_sz will always consist of all records in my table.

=IFERROR(CEILING(pop_sz*NORMSINV(0.5+0.5*0.9)^2*0.05*(0.95)/(0.05^2*(pop_sz-1)+NORMSINV(0.5+0.5*0.9)^2*0.05*(0.95)),1),"")

As a pre-step, I can calculate the population size in SAS as:

proc sql noprint;

select count(*) into :pop_sz

from table_a;

quit;

%put &pop_sz;

An example using a pop_sz = 433 would create a resulting sample size of 47. Could someone provide some guidance on how to convert the Excel code to SAS?  Many thanks in advance.

[Reeza]

The PROBNORM function appears to be what you want. You can map the parameters over by reading the documentation for each function.

Excel

https://support.microsoft.com/en-us/help/827358/excel-statistical-functions-norminv

SAS

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p0am6dtkvnrb09n132vioa1z6oen.htm...

---

@tpt1 wrote:

Hello-

I am trying to convert an Excel formula to SAS code.  I do not have experience with statistics, so this is particularly challenging.  The Excel formula assumes a confidence interval of 90%, a precision rate of 5%, and an exception rate of 5%.  These three values will not change.  The only variable is the population size, which I am labeling in the formula as "pop_sz".  The pop_sz will always consist of all records in my table.

=IFERROR(CEILING(pop_sz*NORMSINV(0.5+0.5*0.9)^2*0.05*(0.95)/(0.05^2*(pop_sz-1)+NORMSINV(0.5+0.5*0.9)^2*0.05*(0.95)),1),"")

As a pre-step, I can calculate the population size in SAS as:

proc sql noprint;

select count(*) into :pop_sz

from table_a;

quit;

%put &pop_sz;

An example using a pop_sz = 433 would create a resulting sample size of 47. Could someone provide some guidance on how to convert the Excel code to SAS?  Many thanks in advance.

---