topic Re: Append unique records from one table to another in SAS Procedures

Append unique records from one table to another

sasbanker — Sun, 10 May 2015 16:21:00 GMT

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.

Re: Append unique records from one table to another

SASKiwi — Mon, 11 May 2015 02:35:16 GMT

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;

Re: Append unique records from one table to another

Kurt_Bremser — Mon, 11 May 2015 06:01:45 GMT

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;

Re: Append unique records from one table to another

SASKiwi — Mon, 11 May 2015 08:53:14 GMT

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;

Re: Append unique records from one table to another

Kurt_Bremser — Mon, 11 May 2015 09:01:30 GMT

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

Re: Append unique records from one table to another

sasbanker — Mon, 11 May 2015 12:21:27 GMT

Thanks. Muchly appreciated.

Re: Append unique records from one table to another

Ksharp — Mon, 11 May 2015 12:31:52 GMT

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;