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

[telligent]

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. 

[Reeza]

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&docs...

[Tom]

---

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

[telligent]

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?

[ballardw · Accepted solution]

---

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

[PaigeMiller]

---

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

[telligent]

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

This article explains them. 

[PaigeMiller]

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

[telligent]

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

[Rick_SAS]

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