Append unique records from one table to another

[sasbanker]

I am new to SAS and have had no training, so please be specific in your response.

I have 2 tables, HRIS & TERM.  Both have the same 138 fields, and key fields of ID.

Some of the records in the TERM table have the same ID as records in the HRIS table.

AI want to add all records from the TERM table to the HRIS, but only if the ID in the TERM table is not in the HRIS table.

[SASKiwi]

Probably a MERGE is the easiest way to do this, assuming your data is already sorted by ID and the ID key is unique in both tables. By using the IN = option you can identify which records are coming from which table. The IF statement will keep all records read from HRIS plus any records from TERM that are not in HRIS.

data HRIS;

  merge HRIS (in = HR)

             TERM (in = TM)

             ;

  by ID;

  if HR or (not HR and TM); <=== This is the same as saying: if HR = 1 or (HR = 0 and TM = 1)

run;

[Kurt_Bremser · Accepted solution]

The problem with that approoach are the shared columns. In case where an ID is present in both tables, TERM values may overwrite HRIS values.

My quick solution:

proc sql;

create table HRIS_new as

select * from HRIS

union all

select b.* from TEMP b left join HRIS c on b.ID = c.ID

where c.ID is missing

;

quit;

[SASKiwi]

Of course Kurt you are quite correct. This variation should work though assuming ID is unique in both datasets:

data HRIS;

set HRIS (in = HR)

       TERM (in = TM)

       ;

  by ID;

  if (HR and first.ID) or (TM and first.ID);

run;

[Kurt_Bremser]

Heh. That's a neat use of the first.ID!

[sasbanker]

Thanks.  Muchly appreciated.

[Ksharp]

I would use Hash Table if the table is big.

Code not tested.

proc sql;

create table HRIS_new as

select * from HRIS

union all

select b.* from TEMP b where b.id in

(

select id from TEMP

except

select id from HRIS

)

;

quit;