topic Re: Remerge-using Have or WHERE in SAS Programming

Remerge-using Have or WHERE

Ronein — Thu, 23 Dec 2021 17:21:46 GMT

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;

Re: Remerge-using Have or WHERE

PaigeMiller — Thu, 23 Dec 2021 17:30:05 GMT

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

Re: Remerge-using Have or WHERE

Reeza — Thu, 23 Dec 2021 17:31:01 GMT

Did you test your theory?
What happened?

Re: Remerge-using Have or WHERE

sbxkoenk — Thu, 23 Dec 2021 17:31:13 GMT

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

Re: Remerge-using Have or WHERE

PaigeMiller — Thu, 23 Dec 2021 17:53:12 GMT

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

Re: Remerge-using Have or WHERE

Ronein — Thu, 23 Dec 2021 18:39:30 GMT

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.

Re: Remerge-using Have or WHERE

Reeza — Thu, 23 Dec 2021 19:03:05 GMT

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

Re: Remerge-using Have or WHERE

PaigeMiller — Thu, 23 Dec 2021 19:36:06 GMT

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

Re: Remerge-using Have or WHERE

novinosrin — Thu, 23 Dec 2021 23:06:31 GMT

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