topic Re: Excel percent.inc() in SAS Programming

Excel percent.inc()

Larrihoover — Thu, 26 Jul 2018 15:11:08 GMT

Is there a pre - built quartile function that ties to excels percent.inc() function? I found one that works in R which I can use proc iml to do the trick but was trying to see if there was one in SAS. I can code the logic if needed but my question was more in search of a pre built in way to do this as follows:

proc means data= look p75 p90 qtldef=1;
class periodid;
var num_lt_660 ;

output out=look_qrtl (drop = _type_ _freq_ ) p75= p90= /autoname;
where periodid = &m_date;
run;

Re: Excel percent.inc()

Reeza — Thu, 26 Jul 2018 15:17:26 GMT

I'm not really clear on what you're looking for, but perhaps these are close.

I usually use PROC RANK with group = 100 to get the percentiles but it can do various other calculations or groupings.

PROC UNIVARIATE will also calculate any percentile level.

Re: Excel percent.inc()

Larrihoover — Thu, 26 Jul 2018 15:42:18 GMT

Based on the R definition, type one is what excel is doing and type 3 is what SAS is doing. I have tried all of the qtldef and pctldef and was wondering if there was a pre built way to tie to the type 1 definition that I did not see.

Q[i](p) = (1 - γ) x[j] + γ x[j+1],

where 1 ≤ i ≤ 9, (j-m)/n ≤ p < (j-m+1)/n, x[j] is the jth order statistic, n is the sample size, the value of γ is a function of j = floor(np + m) and g = np + m - j, and m is a constant determined by the sample quantile type.

Discontinuous sample quantile types 1, 2, and 3

For types 1, 2 and 3, Q[i](p) is a discontinuous function of p, with m = 0 when i = 1 and i = 2, and m = -1/2 when i = 3.

Type 1
Inverse of empirical distribution function. γ = 0 if g = 0, and 1 otherwise.

Type 2
Similar to type 1 but with averaging at discontinuities. γ = 0.5 if g = 0, and 1 otherwise.

Type 3
SAS definition: nearest even order statistic. γ = 0 if g = 0 and j is even, and 1 otherwise.