topic Re: Updated non existing ID's from other table in SAS Programming

Updated non existing ID's from other table

Sathish_jammy — Wed, 06 Mar 2019 07:11:29 GMT

Dear Experts,

I need to update the lib1.Table1 using another table where ID not in lib1.Table1 with corresponding columns (Same col name(30+ columns))

Each table in different library with the same table name and column name.

I want to group it out into single table with only unique IDs.

data lib1.table1; /*need All unique ID in this table*/
input Id var1$ var2 var3 var4 ;/*....var30*/
cards;
111 a 1 5 9
12 v 4 5 3
32 b 1 8 4
54 b 1 3 7
78 f 9 7 8
;

data lib2.table1;
input Id var1$ var2 var3 var4 ;/*....var30*/
cards;
111 s 2 8 7
2 d 7 3 7
3 h 5 7 8
14 g 2 8 7
78 j 1 3 4
;

OP 

111 a 1 5 9
12 v 4 5 3
32 b 1 8 4
54 b 1 3 7
78 f 9 7 8
2 d 7 3 7
3 h 5 7 8
14 g 2 8 7

Please suggest some ideas to resolve it.

Much Thanks.

Re: Updated non existing ID's from other table

Kurt_Bremser — Wed, 06 Mar 2019 07:18:21 GMT

Try a simple SQL approach:

proc sql;
create table want as
  select *
  from table1
union all
  select *
  from table2
  where id not in (select id from table1)
;
quit;

Re: Updated non existing ID's from other table

PeterClemmensen — Wed, 06 Mar 2019 07:23:18 GMT

Like this? (I have changed a data set name to work with the data)

data table1;
input Id var1$ var2 var3 var4 ;
cards;
111 a 1 5 9
12 v 4 5 3
32 b 1 8 4
54 b 1 3 7
78 f 9 7 8
;

data table2;
input Id var1$ var2 var3 var4 ;
cards;
111 s 2 8 7
2 d 7 3 7
3 h 5 7 8
14 g 2 8 7
78 j 1 3 4
;

proc sql;
   create table append as
   select * from table2
   where Id not in (select distinct Id from table1);
quit;

proc append base=table1 data=append;
run;

Re: Updated non existing ID's from other table

Sathish_jammy — Wed, 06 Mar 2019 08:55:13 GMT

Dear @PeterClemmensen @Kurt_Bremser

Thank u for your valuable codes...

What if... to join 10 more tables in the same method.

data lib1.table1;
input Id var1$ var2 var3 var4 ;/*....var30*/
cards;
111 a 1 5 9
12 v 4 5 3
32 b 1 8 4
54 b 1 3 7
78 f 9 7 8
;

data lib2.table1;
input Id var1$ var2 var3 var4 ;/*....var30*/
cards;
111 s 2 8 7
2 d 7 3 7
3 h 5 7 8
14 g 2 8 7
78 f 9 7 8
;
data lib3.table1;
input Id var1$ var2 var3 var4 ;/*....var30*/
cards;
78 g 3 9 5
12 j 4 8 4
43 l 5 7 6
414 w 6 6 8
378 t 7 5 0
;

/*lib10.table1*/

Re: Updated non existing ID's from other table

Kurt_Bremser — Wed, 06 Mar 2019 09:12:48 GMT

Then you need to start with defining logic for special cases. What should happen when you have an id that is not yet in table1, but exists in parallel in more than one of the other tables? Which one should take precedence as the "new" item for the resulting table1?

Re: Updated non existing ID's from other table

s_lassen — Wed, 06 Mar 2019 11:44:08 GMT

Another possibility is to use SORT and MERGE:

proc sort data=lib1.table1;
  by id;
run;

proc sort data=lib2.table1;
  by id;
run;

data want;
  merge lib2.table1 lib1.table1;
  by id;
run;

Note that I put lib2.table1 before lib1.table1 on the MERGE statement; that way, the data from LIB1 overwrites the data from LIB2 if both are present.

If you want to do the same with more tables, just remember to put the tables with highest priority last in the MERGE statement.