Extracting observations which are not common in a variable present in two datsets.

[hiteshchauhan1]

So, the question i'm about to ask is something i'm trying for the first time in sas. Below is what i want to achieve:

I have two data-sets which have a variable which is common in both. Now that variable has some observation which are common in both the data-sets but also there are some values which are not common. Now, i want the list of those variables which are present in one and not in other for both the data-sets.

i can't think of any proc sql or any other statement/function that can help me achieve what i want. Please help me with this.

Thanks

[Jagadishkatam]

Could you please provide a sample data

[Kurt_Bremser]

Several options:

data step:

proc sort data=table1;
by var1;
run;

proc sort data=table2;
by var1;
run;

data exclusions;
merge
  table1 (in=t1 keep=var1)
  table2 (in=t2 keep=var1)
;
by var1;
if t1 and not t2;
if last.t1; /* avoids duplicates */
run;

SQL:

proc sql;
create table exclusions as
select distinct table1.var1
from table1
where var1 not in (select distinct table2.var1 from table2)
;
quit;

Depending on the size of table2, you could also consider to create a "yes/no" format from it, and use that while reading table1 to decide if observations are kept.

Or one could create a hash object from table2 (needs approx. the same amount of memory as the format method).

[hiteshchauhan1]

Thanks @Kurt_Bremser i tried the proc sql one and it worked smoothly. Though, i nees\d a little clarification on this, the output i will get will be observations of var1 from table1 which are not found in table2. Am i getting it right?

[ballardw]

---

@hiteshchauhan1 wrote:

Thanks @Kurt_Bremser i tried the proc sql one and it worked smoothly. Though, i nees\d a little clarification on this, the output i will get will be observations of var1 from table1 which are not found in table2. Am i getting it right?

---

Use a UNION operation between two "directional" selections to get both sides. EXCEPT is another way to do the basic selection.

proc sql;
   create table exclusions as
   (select distinct table1.var1
      from table1
   except
   select distinct table2.var1 from table2)

   union

   (select distinct table21.var1
      from table2
   except
   select distinct table1.var1 from table1)
   ;
quit;

[hiteshchauhan1]

Thanks @ballardw for the help, working flawlessly.