Proc sql: Copy one single row from one table to all rows in another table

Question

I have two tables.

Table A:

a b
----
1 2 
3 4

and a table B with only one row

c d e
------
5 6 7

I want to create a new table which looks like this:

a b c d e
----------
1 2 5 6 7
3 4 5 6 7

i.e copy the single row from B into all rows in A. Note: Using SAS proc sql.

user_10498828 · Answer

First add 3 columns to your TableA:

ALTER TABLE TableA ADD c INTEGER;
ALTER TABLE TableA ADD d INTEGER;
ALTER TABLE TableA ADD e INTEGER;

Then update TableA with the values from TableB:

UPDATE a
SET a.c = b.c, a.d = b.d, a.e = b.e
FROM TableA a CROSS JOIN TableB b;

See the demo.
Results:

| a   | b   | c   | d   | e   |
| --- | --- | --- | --- | --- |
| 1   | 2   | 5   | 6   | 7   |
| 3   | 4   | 5   | 6   | 7   |

user_1919583 · Answer

Data step?

data want;
set A;
if _n_ = 1 then set B;
run;

user_10313496 · Answer

INSERT IGNORE INTO Table A select * from Table B; so basically I am merging the two table, considering that the table DDL is the same.