Dear SAS community,
I need your help to merge two tables with same columns. To be more precise, I have two tables one very large(more than 200 columns and about 6000 rows) and second tables with same columns and just one row. I need to impute values from second table in missing field in first table.
Example:
Table 1
A1 A2 A3 A4 A5 ....
5 6.7 1 . 2
. 2 . 3 .
Table 2
A1 A2 A3 A4 A5 ...
2.4 6.5 5 2.8 1.7
Result
A1 A2 A3 A4 A5 ....
5 6.7 1 2.8 2
2.4 2 5 3 1.7
Any ideas?
Is it possible to set keys for merging as arrays?
Thank you!
data have1; input A1 A2 A3 A4 A5; datalines; 5 6.7 1 . 2 . 2 . 3 . ; data have2; input A1 A2 A3 A4 A5; datalines; 2.4 6.5 5 2.8 1.7 ; data a; set have1 end=last; id+1; if last then call symputx('n',_n_); run; data b; set have2; do id=1 to &n; output; end; run; data want; update b a; by id; drop id; run;
My idea: create a SQL step dynamically from SASHELP.VCOLUMN:
data have1;
input A1 A2 A3 A4 A5;
datalines;
5 6.7 1 . 2
. 2 . 3 .
;
data have2;
input A1 A2 A3 A4 A5;
datalines;
2.4 6.5 5 2.8 1.7
;
data _null_;
set sashelp.vcolumn (where=(libname ="WORK" and memname = "HAVE1")) end=done;
if _n_ = 1 then call execute("
proc sql;
create table want as
select
");
call execute("coalesce(h1." !! strip(name) !! ",h2." !! strip(name) !! ") as " !! strip(name));
if done then call execute("
from have1 h1, have2 h2;
quit;
");
else call execute(",");
run;
data have1; input A1 A2 A3 A4 A5; datalines; 5 6.7 1 . 2 . 2 . 3 . ; data have2; input A1 A2 A3 A4 A5; datalines; 2.4 6.5 5 2.8 1.7 ; data a; set have1 end=last; id+1; if last then call symputx('n',_n_); run; data b; set have2; do id=1 to &n; output; end; run; data want; update b a; by id; drop id; run;
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.