topic Compare two dependant tables without updating one of them in New SAS User

Compare two dependant tables without updating one of them

salvavit — Fri, 05 Jun 2020 14:19:26 GMT

Hi all. I am a bit confused about how to handle this situation:

I have an input table (table A) storing a list of Names, Surnames, IDs, Telephone numbers, Dates of birth and other personal data. This table is ran and updated every day, with new records appended.

From this table, I created another table (table B) containing only IDs and Telephone numbers from table A. This table is likewise updated every day.

What I need to do is to compare the newest version of table A with yesterday's version of table B, i.e. I need to extract the records that have been inserted in table A in the last run. How can I do this?

Re: Compare two dependant tables without updating one of them

Kurt_Bremser — Fri, 05 Jun 2020 14:42:06 GMT

Keep your tables sorted by ID. Then you can do;

data compare;
merge
  a (in=a keep=id)
  b (in=b keep=id)
;
by id;
if a and not b;
run;

before you update B.

Or, in SQL:

proc sql;
create table compare as
  select a.id
  from a
  where a.id not in (select id from b)
;
quit;

Re: Compare two dependant tables without updating one of them

salvavit — Fri, 05 Jun 2020 15:29:11 GMT

Thanks for your reply Kurt.
Unfortunately, I think I can't implement your solution. Both table A
and table B are created and defined in the same SAS program in which I
want to perform that comparison. That is, if I were to compare the two
table before B is updated, this would mean that I am comparing Table A
to a table that does not exist yet.
I am quite new in SAS programming so I might be wrong, but I didn't
find any online support documentation on this matter.

Re: Compare two dependant tables without updating one of them

Kurt_Bremser — Fri, 05 Jun 2020 15:40:55 GMT

So where is the old table, anyway? It must exist somewhere in a permanent library.

Create the new tables in WORK, and then compare the WORK table with its counterpart in the permanent library. After that is done, copy the WORK tables to the permanent location.