Converting Excel sampling formula to SAS code

tpt1 — Wed, 12 Dec 2018 17:33:19 GMT

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.

Re: Converting Excel sampling formula to SAS code

Reeza — Wed, 12 Dec 2018 17:50:47 GMT

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&docsetVersion=9.4&locale=en

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

topic Re: Converting Excel sampling formula to SAS code in New SAS User

Converting Excel sampling formula to SAS code

Re: Converting Excel sampling formula to SAS code