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.
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;
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;
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*/
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.