BookmarkSubscribeRSS Feed
TheChadd
Calcite | Level 5
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
8 REPLIES 8
RichardH_sas
SAS Employee
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. 🙂
prholland
Fluorite | Level 6
I've not been able to get PCTL() to work in PROC SQL. It seems to require multiple arguments to work correctly. 😞

.....Phil
TheChadd
Calcite | Level 5
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.
prholland
Fluorite | Level 6
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
TheChadd
Calcite | Level 5
I'll give it a shot and let you know how I do :).

Cheers!
TheChadd
Calcite | Level 5
Well how about that. Neither Summary Statistics nor Summary Tables let me define a percentile. Am I missing something here?
prholland
Fluorite | Level 6
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
TheChadd
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 6240 views
  • 0 likes
  • 3 in conversation