Remerge-using Have or WHERE

[Ronein]

Hello

In this code using Re-merging technique.

I wonder if need to use WHERE clause or HAVING clause in order to choose only students with Math score below overall mean on Math.

Please note tht I haven't run this code and asked a theoretical question.

PROC SQL;
Create table Wanted  AS
select Gender,
	mean(Math) as average_Math
from ttt
WHERE  Math<average_Math
;
QUIT;
 

[PaigeMiller]

If you make up a simple data set, you can find the answer yourself.

[Reeza]

Did you test your theory?
What happened?

[sbxkoenk · Accepted solution]

Hello,

This does NOT work :

PROC SQL;
Create table Wanted  AS
select   Name , Sex
	   , mean(Height) as average_Height
from sashelp.class
where Height < calculated average_Height
;
QUIT;

And this works well :

PROC SQL;
Create table Wanted  AS
select   Name , Sex
	   , mean(Height) as average_Height
from sashelp.class
having Height < average_Height
;
QUIT;

Koen

[PaigeMiller]

---

@Ronein wrote:

Hello

In this code using Re-merging technique.

I wonder if need to use WHERE clause or HAVING clause in order to choose only students with Math score below overall mean on Math.

Please note tht I haven't run this code and asked a theoretical question.

PROC SQL;
Create table Wanted  AS
select Gender,
	mean(Math) as average_Math
from ttt
WHERE  Math<average_Math
;
QUIT;
 

---

Gosh, I also hate the idea of using PROC SQL for EVERYTHING, especially statistical things (although certainly PROC SQL can do this). There are plenty of other ways to answer this question which would be helpful to learn, and would show nice growth if people branched out from SQL into other SAS PROCs. This also has a lot more power in the long run and for real-world problems. I have this problem in my group (so this isn't directed at anyone in particular)

Example:

proc stdize data=sashelp.class method=mean sprefix=delta_ out=want(where=(delta_height<0));
    var height;
run;

[Ronein]

May you please explain why dinyiu hate using re-meege via proc sql?
As I understand this ability exists only in sas and cannot be done in Microsoft sql language.

[Reeza]

It's not appropriate for the use case you've shown but it has it's purposes.

[PaigeMiller]

---

@Ronein wrote:
May you please explain why dinyiu hate using re-meege via proc sql?
As I understand this ability exists only in sas and cannot be done in Microsoft sql language.

---

I already explained why I feel the way I do. 

[novinosrin]

Hi @Ronein , Your question per se is good, albeit warrants a tutorial approach than a short explanation. 

Short answer:

HAVING clause in general applies for conditional vertical processing for columns as a whole or for BY groups using a summary function, whereas the WHERE clause strictly is not designed and is incompatible for vertical column summary. Therefore WHERE isn't for summary and then filter, rather you could deem this a mere row filter.

To begin with, some teaser for you to figure why both HAVING and WHERE for a calculated column works below-

/*Using calculated & WHERE */
PROC SQL;
Create table Wanted  AS
select   *,mean(100, weight) as w
from sashelp.class
where  calculated w < 100
;
QUIT;

/*Using HAVING*/
PROC SQL;
Create table Wanted  AS
select   *,mean(100, weight) as w
from sashelp.class
having   w < 100
;
QUIT;

Tips- 1. Think about how a SQL processor works i.e. read from disk, buffer, memory(cache), processing 2. Timing - what executes when(before or after) 3. Why there's no REMERGE in the above example?

I will try and come back with a detailed explanation if my boss gives me some time off. 🙂 lol