What is the equivalent of BINOM.DIST.RANGE in SAS?

[ertr]

Hi everybody,

I can calculate BINOM.DIST with the PDF function in SAS, but I'm looking for the equivalent of the BINOM.DIST.RANGE function. Let me give you an example;

DATA Have;
Length Bucket 8 Obs 8 Defaults 8 Avg_PD 8 ;
Infile Datalines Missover Dlm=",";
Input Bucket Obs Defaults Avg_PD;
Format Avg_PD Percent8.2;
Datalines;
1,300,5,0.01
2,1000,20,0.02
3,2000,80,0.04
4,2000,100,0.05
5,2000,150,0.07
6,1000,80,0.08
7,800,100,0.12
8,200,20,0.14
;

DATA WANT;
SET HAVE;
DR=Defaults/Obs;
PValue=PDF('BINOMIAL',Defaults/*number of successes*/,DR /*probability of success*/,Obs/*number of trails*/);
Run;

I'm trying to get the same results in SAS as in the Excel table below.

Bucket	Obs	Defaults	Avg PD	DR	P-Value
1	300	5	1,0%	1,7%	18,39%
2	1000	20	2,0%	2,0%	53,06%
3	2000	80	4,0%	4,0%	51,58%
4	2000	100	5,0%	5,0%	51,43%
5	2000	150	7,0%	7,5%	20,14%
6	1000	80	8,0%	8,0%	51,67%
7	800	100	12,0%	12,5%	34,73%
8	200	20	14,0%	10,0%	96,33%

PValue = =BINOM.DIST.RANGE(Obs;AvgPd;Defaults;Obs)

Your help is greatly appreciated; thanks in advance.

[ballardw]

Define what "BINOM.DIST.RANGE" might actually be.

If you expect to have the range of a binomial distribution it would typically be 0 to number of trials as potential min/max value regardless of probability of success, just very unlikely to see the min or max for some given probability of success.

I don't understand what your example or output table is attempting to say. P-values are the result of tests, not statistics. So I am not at all clear what that relation would be with Range.

Please be very carefully about phrasing function names or similar using non-SAS language constructs. In SAS Binom.dist would be either a library named Binom and data set named dist, or in Sql  a table named or with an alias of Binom and a variable named dist in most common syntax. In a data step and a few other places you could be attempting to use SCL constructs that just do no exist. So for clarity of questions use either SAS syntax or plain language.

[FreelanceReinh]

Hi @ertr,

In your example, where the fourth argument of the Excel function equals the first, you can use the SDF function like this:

data want;
set have;
DR=Defaults/Obs;
p_Value=SDF('BINOMIAL',Defaults-1,Avg_PD,Obs);
format Avg_PD DR nlpct7.1 p_value nlpct8.2;
run;

Edit: In the general case (i.e., with a non-trivial upper limit of the interval in the fourth argument in Excel, let's call it maxDefaults) you can use a difference of two CDF function values:

p=CDF('BINOMIAL',maxDefaults,Avg_PD,Obs)-CDF('BINOMIAL',Defaults-1,Avg_PD,Obs);

If you omit the fourth argument in Excel, you get the value of the (SAS) PDF function:

p=PDF('BINOMIAL',Defaults,Avg_PD,Obs);