Can someone please explain how pctlpts option in univariate works?

[VinayakKankate]

I am confused with what exactly is been calculated with help of pctlpts

For Ex:
proc univariate data=abc noprint;
var a;
by b.c;
output out=xyz pctlpts=5 95 pctlpre=Perc_ pctlname=P5 P95 sum=balance;
run;

[VinayakKankate]

I am confused with what exactly is been calculated with help of pctlpts

For Ex:
proc univariate data=abc noprint;
var a;
by b.c;
output out=xyz pctlpts=5 95 pctlpre=Perc_ pctlname=P5 P95 sum=balance;
run;

[Reeza]

Are you asking what is a percentile? 

P5 is the 5th percentile and is a value in your dataset that means that 5% of the data is below this value and 95% is above. 

If you think of having 100 numbers, from 1 to 100 then 5 is the 5th percentile. In a different dataset you would sort the data and find the 5th percentile. The exact calculation can vary slightly depending on how you account for ties in your data. 

For P95, 5% of values are greater than the value amd 95% are lower. 

And a simple example/answer

https://www.mathsisfun.com/data/percentiles.html

PS - please post your question only once. 

[VinayakKankate]

@Reeza Thank you so much really appreciate your help.

I am trying to implement this in SQL Server can you please guide on this.

I have tried multiple things but no luck

[Reeza]

PROC SQL doesn't support calculating percentiles. 

[RW9]

If your trying to re-create this in SQL Server, you would be better off trying an SQL Server forum.  To my mind, SQL is not an analysis tool, it only provides some basic aggregations, therefore it would not be my choice to do this kind of programming.  I am not saying its not possible, but why go to all the effort of developing some software, validating that software, documenting and lifecycle managing that software, if you already have SAS proc univariate which does this?  SAS and DB's can connect pretty easily.

[VinayakKankate]

@RW9 Thanks for your advise but it is a business task and we need to implement SAS programs in SQL procedures,

and we are new to SAS thus asking for help

[Reeza]

Basically your a consultant and transferring SAS programs to SQL. 

A quick google search generated PERCENTILE_DESC(). You should be able to use the SQL documentation from here.