Merging tables with same columns

[miner_girl]

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!

[Kurt_Bremser]

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;

[Ksharp · Accepted solution]

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;