topic Re: SAS SQL on/where clauses in New SAS User

SAS SQL on/where clauses

anming — Wed, 02 Dec 2020 16:08:39 GMT

proc sql;
	create table inner_one As 
	select g.stu_id, score1, score3 
	from score_data_g as g inner join 
		score_data_ng as ng 
		on g.stu_id=ng.stu_id;
/* on score_data_g.stu_id = score_data_ng.stu_id; */
quit;

proc sql;
	create table inner_one As 
	select g.stu_id, score1, score3 
	from score_data_g as g, 
		score_data_ng as ng 
/* 		where g.stu_id=ng.stu_id; */
/* where score_data_g.stu_id = score_data_ng.stu_id; */
where g.stu_id = ng.stu_id;
quit;

In two codes above, the on clause in inner join can be replaced by the where clause. But it does not work for outer join
(left, right and full join). In both the where clause and the inner join, it seems that order of two datasets does
not make different.

Does this correct?

Re: SAS SQL on/where clauses

Reeza — Wed, 02 Dec 2020 16:10:36 GMT

Code blocks are for code, not your question.
You may want to define what you mean by "WORK" here, because the code will run and execute without errors. You may be able to replace your ON with WHERE but it may not be as simple as just changing ON to WHERE.

Re: SAS SQL on/where clauses

Kurt_Bremser — Wed, 02 Dec 2020 16:24:52 GMT

Using a WHERE in SQL is equivalent to an inner join.

Re: SAS SQL on/where clauses

anming — Wed, 30 Dec 2020 03:30:35 GMT

For an inner join of two data sets "on" clause is used and two data sets are listed with "inner join" but for 'where' two data sets are separated by a comma.