Desktop productivity for business analysts and programmers

Using Query builder to create percentile stats

Reply
Contributor
Posts: 72

Using Query builder to create percentile stats

I have a simple table of two columns: Week and Value (numeric, continuous). Ultimately I want to plot the 99.8th percentile of the Value by Workweek. Using the Query Builder on the table, I tried using PCTL function: (PCTL(99.8,Value). However, it will not support grouping for this function in the interface. I have also tried doing this manually:
PROC SQL;
CREATE TABLE SASUSER.temp1 AS
SELECT t1.Week,
(PCTL(99.8,t1.'OQM-FT1 Hrs'n)) FORMAT=BEST7.2 AS '99.8th percentile'n
FROM SASUSER.Table t1
GROUP BY t1.Week;
QUIT;
I get the Warning in the log:
"WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function."

So this just returns the Value, instead of the 99.8th percentile of the Value by Week.

Any ideas??

Cheers!
Chadd
SAS Employee
Posts: 149

Re: Using Query builder to create percentile stats

I've always used the Summary Statistics task to do percentiles. I'm not 100% sure that the query builder can even do percentiles... but I'm happy to be proven wrong. Smiley Happy
Frequent Contributor
Posts: 81

Re: Using Query builder to create percentile stats

Posted in reply to RichardH_sas
I've not been able to get PCTL() to work in PROC SQL. It seems to require multiple arguments to work correctly. Smiley Sad

.....Phil
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

Posted in reply to RichardH_sas
I just want a data table with the 99.8th percentile by week to show up on my process flow. Can Summay Stats do that? When I played around with it, it would output the summary table graphically, but not as a usable table in my process flow.
Frequent Contributor
Posts: 81

Re: Using Query builder to create percentile stats

You should be able to save the results data from Summary Stats in a SAS data set, which you'll be able to use elsewhere.

........Phil
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

Posted in reply to prholland
I'll give it a shot and let you know how I do Smiley Happy.

Cheers!
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

Well how about that. Neither Summary Statistics nor Summary Tables let me define a percentile. Am I missing something here?
Frequent Contributor
Posts: 81

Re: Using Query builder to create percentile stats

You can get 99.8% percentiles from the Tables > "Basic confidence intervals" in Distribution Analysis (PROC UNIVARIATE). You can then "Save output statistics to a data set".

.........Phil
Contributor
Posts: 72

Re: Using Query builder to create percentile stats

Posted in reply to prholland
Looks like it only spits out the 99.8 LCL and UCL for the mean. I gave up and just coded in a proc univariate and merged it with the other output table.
proc univariate data=sasuser.table noprint;
var Values;
by Week;
output out=pctl pctlpts=99.8 pctlpre=Values;

If I have to manually code any more items in this project, I'll probably drop EG altogether and use plain 'ol SAS.
Ask a Question
Discussion stats
  • 8 replies
  • 3860 views
  • 0 likes
  • 3 in conversation