Help using Base SAS procedures

How to select columns which does not match in two tables

Reply
Frequent Contributor
Posts: 112

How to select columns which does not match in two tables

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.

Respected Advisor
Posts: 3,748

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

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;

Ask a Question
Discussion stats
  • 1 reply
  • 110 views
  • 1 like
  • 2 in conversation