Help using Base SAS procedures

Append unique records from one table to another

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Append unique records from one table to another

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.


Accepted Solutions
Solution
‎05-11-2015 02:01 AM
Super User
Posts: 6,938

Re: Append unique records from one table to another

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 3,106

Re: Append unique records from one table to another

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;

Solution
‎05-11-2015 02:01 AM
Super User
Posts: 6,938

Re: Append unique records from one table to another

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,106

Re: Append unique records from one table to another

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;

Super User
Posts: 6,938

Re: Append unique records from one table to another

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 9

Re: Append unique records from one table to another

Thanks.  Muchly appreciated.

Super User
Posts: 9,681

Re: Append unique records from one table to another

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 418 views
  • 0 likes
  • 4 in conversation