topic Merging tables with same columns in SAS Programming

Merging tables with same columns

miner_girl — Tue, 04 Aug 2020 11:51:38 GMT

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!

Re: Merging tables with same columns

Kurt_Bremser — Tue, 04 Aug 2020 12:12:21 GMT

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;

Re: Merging tables with same columns

Ksharp — Tue, 04 Aug 2020 12:48:44 GMT

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;