topic Re: full join in proc sql in SAS Programming

full join in proc sql

Naveen1111 — Sat, 06 Jun 2020 17:52:49 GMT

Hi All,

I have a question, as I just started learning sas. There are two datasets i.e.(abc, qwe). In dataset ABC we have two obs, but when I used with another datatset (qwe) by using full join, instead of getting two obs, getting three obs on both the side. Below are the mentioned dataset and output.

could someone tell me why I m getting for dataset abc

data abc;
input a b c d$;
cards;
10 20 30 "M"
89 90 90 "F"
;
run;

data qwe;
input q w c e$;
cards;
10 20 30 "M"
89 90 90 "M"
89 90 90 "M"
;
run;

proc sql;
select* from abc
full join
qwe on
abc. c=qwe. c;
quit;

OUTPUT

a b c d q w c e
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
10 20 30 "M" 10 20 30 "M"
89 90 90 "F" 89 90 90 "M"
89 90 90 "F" 89 90 90 "M"

Re: full join in proc sql

novinosrin — Sat, 06 Jun 2020 18:17:27 GMT

Hi @Naveen1111

SQL JOIN Thumb rule : N*M values -->

So will the below make sense

abc_data	qwe_data	Relationship
c	c	Notes
30	30	One to one
90	90	One to many
	90	One to many

Re: full join in proc sql

Kurt_Bremser — Sat, 06 Jun 2020 18:19:10 GMT

You have one match for c=30, and two matches for c=90, so you get three observations in the result. If you want only two, you need to deduplicate dataset qwe.

Re: full join in proc sql

Naveen1111 — Sun, 07 Jun 2020 13:49:04 GMT

Hi,

Thanks for ur support!

Could you tell me how can I further deduplicate it (as per my knowledge full join combines the result of both left and right outer join)

Also, please help me with the sequence/order of SQL syntax as I tried to execute the below program, but it didn't work .

Need to know where should I terminate each statement and in which order, should I use order by syntax first or group by or where condition?

proc sql;
create table ph as select* from sashelp.class
where age>14 order by weight desc group by sex;
quit;

Re: full join in proc sql

Kurt_Bremser — Sun, 07 Jun 2020 20:00:13 GMT

Given your data as posted, a distinct should do it:

proc sql;
select distinct
  a.*,
  q.q,
  q.w,
  q.c as c_q,
  q.e
from abc a
full join
qwe q
on a.c = q.c
;
quit;

"Did not work" is worthy of the proverbial blonde secretary. Supply the log, and describe in detail where the result did not meet your expectactions.

The most obvious problem is the missing blank between select and the asterisk. Also do not use the asterisk like this in joins, or you'll get WARNINGs.