topic Re: How to select columns which does not match in two tables in SAS Procedures

How to select columns which does not match in two tables

renjithr — Sun, 29 Sep 2013 07:39:25 GMT

Hi,

I have the following two tables and I want to get the rows from both tables where the key does not match using PROC SQL.

Data Company;

input Deptid name$10. ;

datalines;

101 ram

102 dam

103 fff

104 ggg

105 sss

106 aaa

;

run;

proc print data=company;run;

Data Dept;

input Deptid noe;

datalines;

101 100

103 30

201 200

301 20

;

run;

Desired output:

Deptid Name Noe

102 dam

104 ggg

105 sss

106 aaa

201 200

301 20

Please share your thoughts.

Re: How to select columns which does not match in two tables

Patrick — Sun, 29 Sep 2013 09:34:56 GMT

Have a read of the following link. I consider this quite helpful information.

SAS(R) 9.2 SQL Procedure User's Guide

As a variation of "Producing Rows from the First Query or the Second Query" below code should do:

proc sql;
create table want as

    select * from Company
      where Deptid not in (select Deptid from Dept)
    outer union corr
    select * from Dept
      where Deptid not in (select Deptid from Company)
;
quit;