How to extrapolate account data included in 1 table but not the other-CodePudding

I have account data from table A and account data from table B. I want to remove accounts included in both tables and view accounts contained in A but not B and vice versa. Accounts are listed by account number in both tables. Is there a way to do this in SAS or SQL?

CodePudding user response：

Assuming the datasets are using the same structure and that account numbers do not appear multiple times in either dataset then a simple MERGE should do it.

If you just want one dataset with all accounts that are not in both then do:

data want;
  merge a(in=ina) b(in=inb);
  by account;
  if not (ina and inb);
run;

If you want two (or three) datasets you could do:

data aonly bonly both;
  merge a(in=ina) b(in=inb);
  by account;
  if (ina and inb) then output both;
  else if ina then output aonly;
  else output bonly;
run;

CodePudding user response：

This won't change your data sets in any fashion but you can see these differences between data sets using PROC COMPARE. Note that if you have duplicates of ID's in each data set, eg. Data set A has the ID 3 in 4 different rows this will not work and neither will a data step.

*assumes data is sorted by ID;
proc compare base=TableA compare=TableB;
var ID;
run;

The variables don't necessarily need the same name either. See this example in the documentation for further reference.