BookmarkSubscribeRSS Feed
VinayakKankate
Calcite | Level 5

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;

7 REPLIES 7
VinayakKankate
Calcite | Level 5

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

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
Calcite | Level 5

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

PROC SQL doesn't support calculating percentiles. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

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. 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 3248 views
  • 0 likes
  • 3 in conversation