topic percentiles in sas procs (univariate means ranks) vs. percentile function in excel in Statistical Procedures

percentiles in sas procs (univariate means ranks) vs. percentile function in excel

telligent — Fri, 27 Mar 2020 21:16:11 GMT

Could someone tell me the difference between a percentile calculation in excel or r vs. sas procedures. I get different answers. If it makes any difference, the numbers have like 20 decimal places. Thanks.

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

Reeza — Fri, 27 Mar 2020 21:55:20 GMT

There isn't a single definition for percentiles, there are 5+ definitions I'm aware of and if you check the PROC UNIVARIATE documentation it goes over some of the different options and how they differ.

I don't know if there's a way to match excel's but I'd recommend against using Excel.

https://documentation.sas.com/?docsetId=procstat&docsetTarget=procstat_univariate_details14.htm&docsetVersion=9.4&locale=en

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

Tom — Fri, 27 Mar 2020 22:02:46 GMT

@telligent wrote:

Could someone tell me the difference between a percentile calculation in excel or r vs. sas procedures. I get different answers. If it makes any difference, the numbers have like 20 decimal places. Thanks.

I know SAS uses IEEE 64 bit floating point numbers. And I think Excel does also. 20 decimal places is more decimal places that can be represented exactly with that level of precision.

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

telligent — Fri, 27 Mar 2020 22:06:57 GMT

Actually excel has two functions, percentile.inc and percentile.exc. SAS nearly matches the exc version. Is there anyway to get SAS to do the inc. version?

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

ballardw — Fri, 27 Mar 2020 22:34:51 GMT

@telligent wrote:

Actually excel has two functions, percentile.inc and percentile.exc. SAS nearly matches the exc version. Is there anyway to get SAS to do the inc. version?

The Univariate procedure has an option PCTLDEF that defines the method to calculate percentiles, with optional values 1 through 5. The default is 5. I don't know what the difference may be between the Excel functions so leave it to you to find a match. Another option may be Proc Rank with groups of 100 (though the results will be 0 to 99) which has a number of ways of using the tied values of a variable, which is likely the main difference involved since percentiles are order statistics.

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

Rick_SAS — Sat, 28 Mar 2020 11:07:51 GMT

Percentiles are also called quantiles. Others have mentioned that there are many definitions to obtain the sample quantiles. If you would like to learn more about the five methods that SAS supports, see "Quantile definitions in SAS."

In fact, there are even definitions that SAS does not (natively) support. I compare all nine definitions show how to compute them all in SAS in the article, "Sample quantiles: A comparison of 9 definitions."

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

PaigeMiller — Sat, 28 Mar 2020 11:32:00 GMT

@telligent wrote:

Actually excel has two functions, percentile.inc and percentile.exc. SAS nearly matches the exc version. Is there anyway to get SAS to do the inc. version?

Tell us exactly the formula that percentile.inc uses.

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

telligent — Sat, 28 Mar 2020 17:20:03 GMT

https://access-excel.tips/excel-percentile-inc-vs-percentile-exc/

This article explains them.

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

PaigeMiller — Sat, 28 Mar 2020 18:18:40 GMT

Then it should be clear that percentile.inc does not have an exact equivalent in SAS.

Re: percentiles in sas procs (univariate means ranks) vs. percentile function in excel

telligent — Sat, 28 Mar 2020 18:23:51 GMT

Yes, I tried all 5 and none matched. I haven't tried the additional 4 that Rick_SAS posted yet.