Different results for percentiles in SAS and Excel

Question

I am trying to get percentiles in SAS. I have got the percentiles in Excel and I am expecting the same results in SAS too, but when I get the percentiles in SAS it is different than in excel.

I am using below sample data, 1 2 3 4 5 6 7 8 9 10

Excel formula I have used is =PERCENTILE(A1:A10, K)

(the value of K is 0.05, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9)

In excel I get the result as

1.45, 1.9, 2.8, 3.7, 4.6, 5.5, 6.4, 7.3, 8.2, 9.1

In SAS I am using below code

data nums;
input no 9.;
datalines; 
1
2
3
4
5
6
7
8
9
10
;
run;

proc univariate data = nums noprint;
var no;
output out = pctls
        pctlpts = 5 10 20 30 40 50 60 70 80 90 
        pctlpre = no
        pctlname = pct5 pct10 pct20 pct30 pct40 pct50 pct60 pct70 pct80 pct90;
run;

In SAS I get the result as

1, 1.5, 2.5, 3.5, 4.5, 5.5, 6.5, 7.5, 8.5, 9.5

I get different results.

Am I doing it wrong?

Need your assistance.

They use different interpolation methods. See here analyse-it.com/blog/2013/2/… — dashnick

ashgrim ashgrim · Accepted Answer · 2019-04-30T22:19:40

Code your own percentile function in SAS, using you sample data here’s a pseudocode step by step

Sample=[1,2,3,4,5,6,7,8,9,10]
Sample size n=10
Percentile p= 0.9
(n-1)=9 (size of the array minus 1)
(n-1)p=(9*0.9)= 8.1
Integer part of (n-1)p, I = 8
Float part of (n-1)p, F=0.1
Supposing the first element of the array index is 1 and not 0
If F=0 then
  Result = Sample[I+1]
Else 
 Result= Sample[I+1]+F(Sample[I+2]-Sample[I+1]
end
Since (n-1)p has a decimal component we take the else route of the If statement, using values:
  Result= 9+0.1(10-9)
Result=9.1

Different results for percentiles in SAS and Excel

1 Answers