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"
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 |
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.