BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MaggieFM
Calcite | Level 5

Hi all - my office does performance metrics on employees where they report on the fastest 90% of cases complete.  I get the fastest 90% by creating a series of tables: 

 

data New;
	set PSS.CSBadj;
	count+1;
	by Adj;

	if first.Adj then
		count=1;
run;

Proc sql;
	Create table PSS.CSBAdj90 as Select Adj, PWPTimeliness, Branch, Team, 
		max(count) as max_count From work.New Group by Adj Having 
		count<=(.9*max_count);
Quit;

I then use proc tabulate to create a table showing the number of cases and the average timeliness for the fastest 90% per employee. 

 

My issue is that if someone only has 1 case, then that Timeliness needs to be reported.  Is there any way for me to make the having clause conditional? So that it only applies if max_count>1? 

 

Also if there is a better way to go about calculating the fastest 90% or anywhere else I am all ears! Thanks for your help. 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Hi @MaggieFM  Not sure if i thoroughly understand you, but guessing whether you are meaning something like this?

 

Having 
		count<=(.9*max_count) and count>1 or count=1;

to account or single count entry?

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

Hi @MaggieFM  Not sure if i thoroughly understand you, but guessing whether you are meaning something like this?

 

Having 
		count<=(.9*max_count) and count>1 or count=1;

to account or single count entry?

MaggieFM
Calcite | Level 5

Yes! That was exactly what I meant... thank you for solving that so quickly!!!

Tom
Super User Tom
Super User

Are you just asking for this?

having count<=(.9*max_count)
   and count>1
MaggieFM
Calcite | Level 5

I'm not sure why but this solution didn't work for me - the one above did though. Thanks so much for your help!

Tom
Super User Tom
Super User

@MaggieFM wrote:

I'm not sure why but this solution didn't work for me - the one above did though. Thanks so much for your help!


I depends on whether you want the cases where COUNT=1 to be included or not included.

Also on what MAX_COUNT is.  If COUNT=1 and MAX_COUNT=1 is that to be included or not?  What about if COUNT=1 and MAX_COUNT > 1 ?

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 631 views
  • 0 likes
  • 3 in conversation