How to do this using Proc SQL?

[littlestone]

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?

[MikeZdeb]

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]

Thank you.

Is it possible to do the task using JOIN method?

[Ksharp · Accepted solution]

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]

Thank you so much for your recommendation.

Best regards.

[Howles]

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]

Thank you.

where n(mainflag, minorflag) EQ 1 ;

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

[art297]

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]

thank you very much for clarificiation

[Joeldw]

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]

I tried. It does not work.

thanks.

[Joeldw]

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

Cheers.