BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasbanker
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

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
Super User

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
PROC Star

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;

sasbanker
Calcite | Level 5

Thanks.  Muchly appreciated.

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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