Hi, all
Suppose I have two data sets Main and Minor.
Dataset Main:
Key
1
2
3
4
5
Dataset Minor:
Key
1
3
5
7
9
I know Inner Join on Key will give:
Key
1
3
5
Now I want to get the result:
Key
2
4
7
9
I know how to do it using data step; however, how to do it using Proc SQL?
But I recommend you to use UNION.
data main; input key @@; datalines; 1 2 3 4 5 ; run; data minor; input key @@; datalines; 1 3 5 7 9 ; run; proc sql; create table want as select coalesce(a.key,b.key) as k from main as a full join minor as b on a.key = b.key where a.key is missing or b.key is missing; quit;
Ksharp
hi ... with assistance from online help ...
data main;
input key @@;
datalines;
1 2 3 4 5
;
run;
data minor;
input key @@;
datalines;
1 3 5 7 9
;
run;
proc sql;
create table new as
(select * from main except select * from minor)
union
(select * from minor except select * from main);
quit;
Thank you.
Is it possible to do the task using JOIN method?
But I recommend you to use UNION.
data main; input key @@; datalines; 1 2 3 4 5 ; run; data minor; input key @@; datalines; 1 3 5 7 9 ; run; proc sql; create table want as select coalesce(a.key,b.key) as k from main as a full join minor as b on a.key = b.key where a.key is missing or b.key is missing; quit;
Ksharp
Thank you so much for your recommendation.
Best regards.
I would probably use this approach:
proc sql ;
create table want as
select coalesce(main2.key, minor2.key) as key
from (select key , 1 as mainflag from main ) as main2
full join
(select key , 1 as minorflag from minor) as minor2
on main2.key EQ minor2.key
where n(mainflag, minorflag) EQ 1 ;
quit ;
Some of the other techniques might encounter difficulties
littlestone wrote:
Thank you.
Is it possible to do the task using JOIN method?
Thank you.
where n(mainflag, minorflag) EQ 1 ;
would you please explain to me what does this "n" statement mean?
That is just a SAS function that counts the number of non-missing values. see: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245918.htm
thank you very much for clarificiation
How about a full join of major and minor on minor.key = major.key. Then, in the where statement main.key is null OR minor key is null.
I tried. It does not work.
thanks.
Hello - I was about to give you code for this approach but I see @ksharp has done so already.
Cheers.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.