topic Re: Join datasets by group in sql in SAS Procedures

Join datasets by group in sql

xyxu — Tue, 08 Jun 2021 16:41:55 GMT

How to implement the following in proc sql?

proc sort data = set1; by x, y; run;
proc sort data = set2; by x, y; run;

data set3;
merge set1 (in = a) set2 (in = b);
by x y;
if a and not b;
run;

I am trying to do this by group, and the group identifier is commonly available in set1 and set2.

Re: Join datasets by group in sql

ballardw — Tue, 08 Jun 2021 16:55:39 GMT

Are there variables in common between set1 and set2 other than X and Y?

Data step merge will "update" common variable other than the by variables and if you need/want that behavior we need to know.

BTW your sort syntax is incorrect. No commas in a BY statement

Re: Join datasets by group in sql

xyxu — Tue, 08 Jun 2021 17:08:05 GMT

Yes, there are common variables between the two sets. Let's call them variable z. In the sql, set2 is only used to kick out observations in set1.
You are right that my proc sort should not include commas in the by statement.

Re: Join datasets by group in sql

Reeza — Tue, 08 Jun 2021 17:18:10 GMT

Left or Right join.

See this previous questions for a good diagram (bottom) and reference to a paper that has more details.
https://communities.sas.com/t5/SAS-Procedures/Proc-SQL-equiv-of-Merge-If-A-or-B/td-p/255720

@xyxu wrote:

How to implement the following in proc sql?
proc sort data = set1; by x, y; run;
proc sort data = set2; by x, y; run;

data set3;
merge set1 (in = a) set2 (in = b);
by x y;
if a and not b;
run;
I am trying to do this by group, and the group identifier is commonly available in set1 and set2.

Re: Join datasets by group in sql

novinosrin — Tue, 08 Jun 2021 17:51:40 GMT

proc sql;
  create table set3 as 
  select *
  from
  set1 a left join set2 b
  on a.x=b.x and a.y=b.y
  where a.x ne b.x and a.y ne b.y;
quit;

Note: I used * in select clause coz I am lazy to type and that will throw a warning because of ambiguity. However, you could type the list of variables that you need appropriately.

Re: Join datasets by group in sql

Kurt_Bremser — Tue, 08 Jun 2021 18:34:32 GMT

proc sql;
create table set3 as
  select t1.*
  from set1 t1 left join set2 t2
  on t1.x = t2.x and t1.y = t2.y
  where missing(t2.x) or missing(t2.y)
;
quit;

Re: Join datasets by group in sql

xyxu — Tue, 08 Jun 2021 18:58:28 GMT

Why should we use "and" in the where clause, instead of "or" ? My goal is to find all obs in set1 that does not have any match in set2 in terms of a pair of x and y.

Re: Join datasets by group in sql

novinosrin — Tue, 08 Jun 2021 19:44:54 GMT

"My goal is to find all obs in set1 that does not have any match in set2 in terms of a pair of x and y."--

@xyxu Yeah OR is just fine too

on a.x=b.x and a.y=b.y
where a.x ne b.x or a.y ne b.y;

Re: Join datasets by group in sql

tarheel13 — Fri, 11 Jun 2021 13:04:44 GMT

Just use set operator?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0vo2lglyrnexwn14emi8m0jqvrj.htm

Re: Join datasets by group in sql

Sajid01 — Sun, 27 Jun 2021 17:52:28 GMT

I suggest you stick with data step merge (in comparison to proc SQL).

The already have a code that needs some modifications as follows and you will be nearing your goal.
As suggested by @ballardw make corrections to the sort syntax.

In addition rename the common variables (other than by variables) in one of the datasets.

You may have to do home post processing to the merged dataset to get exactly what you want,

but you can easily achieve your objective of purging the rows of dataset2 from dataset1.