BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
littlestone
Fluorite | Level 6

Hi, all

Suppose I have two data sets Main and Minor.

Dataset Main:

Key

1

2

3

4

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

11 REPLIES 11
MikeZdeb
Rhodochrosite | Level 12

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;

littlestone
Fluorite | Level 6

Thank you.

Is it possible to do the task using JOIN method?

Ksharp
Super User

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

littlestone
Fluorite | Level 6

Thank you so much for your recommendation.

Best regards.

Howles
Quartz | Level 8

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

  • when there are composite keys
  • when there are missing-values in the keys
  • when there are satellite variables

littlestone wrote:

Thank you.

Is it possible to do the task using JOIN method?

littlestone
Fluorite | Level 6

Thank you.

where n(mainflag, minorflag) EQ 1 ;

would you please explain to me what does this "n" statement mean?

art297
Opal | Level 21

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

littlestone
Fluorite | Level 6

thank you very much for clarificiation

Joeldw
Calcite | Level 5

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.

littlestone
Fluorite | Level 6

I tried. It does not work.

thanks.

Joeldw
Calcite | Level 5

Hello - I was about to give you code for this approach but I see @ksharp has done so already.

Cheers.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 2369 views
  • 6 likes
  • 6 in conversation