BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
telligent
Quartz | Level 8

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

9 REPLIES 9
Reeza
Super User

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
Super User Tom
Super User

@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
Quartz | Level 8

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
Super User

@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
Diamond | Level 26

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

--
Paige Miller
PaigeMiller
Diamond | Level 26

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

 
--
Paige Miller
telligent
Quartz | Level 8

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

Rick_SAS
SAS Super FREQ

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

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 4422 views
  • 4 likes
  • 6 in conversation