topic Re: How to do this using Proc SQL? in SAS Procedures

How to do this using Proc SQL?

littlestone — Mon, 21 Nov 2011 02:24:47 GMT

Hi, all

Suppose I have two data sets Main and Minor.

Dataset Main:

Key

Dataset Minor:

Key

I know Inner Join on Key will give:

Key

Now I want to get the result:

Key

I know how to do it using data step; however, how to do it using Proc SQL?

Re: How to do this using Proc SQL?

MikeZdeb — Mon, 21 Nov 2011 02:34:48 GMT

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;

How to do this using Proc SQL?

littlestone — Mon, 21 Nov 2011 02:57:04 GMT

Thank you.

Is it possible to do the task using JOIN method?

How to do this using Proc SQL?

Joeldw — Mon, 21 Nov 2011 03:22:23 GMT

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.

How to do this using Proc SQL?

Ksharp — Mon, 21 Nov 2011 06:49:27 GMT

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

How to do this using Proc SQL?

littlestone — Mon, 21 Nov 2011 15:10:54 GMT

Thank you so much for your recommendation.

Best regards.

How to do this using Proc SQL?

littlestone — Mon, 21 Nov 2011 15:11:26 GMT

I tried. It does not work.

thanks.

How to do this using Proc SQL?

Joeldw — Mon, 21 Nov 2011 19:40:49 GMT

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

Cheers.

Re: How to do this using Proc SQL?

Howles — Mon, 21 Nov 2011 21:19:56 GMT

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?

How to do this using Proc SQL?

littlestone — Tue, 22 Nov 2011 18:27:09 GMT

Thank you.

where n(mainflag, minorflag) EQ 1 ;

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

How to do this using Proc SQL?

art297 — Tue, 22 Nov 2011 18:33:12 GMT

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

How to do this using Proc SQL?

littlestone — Wed, 23 Nov 2011 15:10:51 GMT

thank you very much for clarificiation