topic Re: Conditional Having Clause in New SAS User

Conditional Having Clause

MaggieFM — Thu, 23 Jan 2020 20:59:19 GMT

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.

Re: Conditional Having Clause

novinosrin — Thu, 23 Jan 2020 21:11:10 GMT

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?

Re: Conditional Having Clause

Tom — Thu, 23 Jan 2020 21:14:19 GMT

Are you just asking for this?

having count<=(.9*max_count)
   and count>1

Re: Conditional Having Clause

MaggieFM — Thu, 23 Jan 2020 21:24:20 GMT

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

Re: Conditional Having Clause

MaggieFM — Thu, 23 Jan 2020 21:24:44 GMT

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

Re: Conditional Having Clause

Tom — Thu, 23 Jan 2020 21:31:36 GMT

@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 ?