Help using Base SAS procedures

How to do this using Proc SQL?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

How to do this using Proc SQL?

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?


Accepted Solutions
Solution
‎11-21-2011 01:49 AM
Super User
Posts: 10,035

How to do this using Proc SQL?

Posted in reply to littlestone

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


All Replies
Valued Guide
Posts: 765

Re: How to do this using Proc SQL?

Posted in reply to littlestone

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;

Frequent Contributor
Posts: 89

How to do this using Proc SQL?

Thank you.

Is it possible to do the task using JOIN method?

Solution
‎11-21-2011 01:49 AM
Super User
Posts: 10,035

How to do this using Proc SQL?

Posted in reply to littlestone

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

Frequent Contributor
Posts: 89

How to do this using Proc SQL?

Thank you so much for your recommendation.

Best regards.

Regular Contributor
Posts: 184

Re: How to do this using Proc SQL?

Posted in reply to littlestone

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?

Frequent Contributor
Posts: 89

How to do this using Proc SQL?

Thank you.

where n(mainflag, minorflag) EQ 1 ;

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

PROC Star
Posts: 7,474

How to do this using Proc SQL?

Posted in reply to littlestone

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

Frequent Contributor
Posts: 89

How to do this using Proc SQL?

thank you very much for clarificiation

Occasional Contributor
Posts: 5

How to do this using Proc SQL?

Posted in reply to littlestone

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.

Frequent Contributor
Posts: 89

How to do this using Proc SQL?

I tried. It does not work.

thanks.

Occasional Contributor
Posts: 5

How to do this using Proc SQL?

Posted in reply to littlestone

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

Cheers.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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