topic Re: Can someone please explain how pctlpts option with univariate works? in SAS Enterprise Guide

Can someone please explain how pctlpts option in univariate works?

VinayakKankate — Thu, 08 Dec 2016 06:27:27 GMT

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;

Can someone please explain how pctlpts option with univariate works?

VinayakKankate — Thu, 08 Dec 2016 06:53:46 GMT

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;

Re: Can someone please explain how pctlpts option with univariate works?

Reeza — Thu, 08 Dec 2016 07:06:17 GMT

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.

Re: Can someone please explain how pctlpts option with univariate works?

VinayakKankate — Thu, 08 Dec 2016 08:59:48 GMT

@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

Re: Can someone please explain how pctlpts option with univariate works?

Reeza — Thu, 08 Dec 2016 09:40:37 GMT

PROC SQL doesn't support calculating percentiles.

Re: Can someone please explain how pctlpts option with univariate works?

RW9 — Thu, 08 Dec 2016 09:41:04 GMT

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.

Re: Can someone please explain how pctlpts option with univariate works?

VinayakKankate — Thu, 08 Dec 2016 09:52:10 GMT

@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

Re: Can someone please explain how pctlpts option with univariate works?

Reeza — Thu, 08 Dec 2016 09:55:57 GMT

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.