PROC sql: insert into

[current_thing]

I have two SAS data sets with a common column called key. I want to insert into table 2 a column called bill_complete_dt. I want to do a right join where key on both tables match. I try this:

proc sql;

insert into my.&file2
select a.bill_complete_dt from my.&file1 as a right join my.&file2 as b on a.key=b.key;

quit;

I get the following error mesg:

 ERROR: Attempt to insert fewer columns than specified after the INSERT table name.
ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

What I'm I doing wrong? Thanks for any help

[Kurt_Bremser · Accepted solution]

INSERT INTO adds complete observations, and the values must match the structure of the existing dataset. You cannot use the statement to add a column to the structure of the dataset. For this, you need to rewrite the dataset, either with CREATE TABLE in SQL, or with a DATA step.

proc sql;
create table my.file3 as /* do not immediately overwrite a dataset, in case something goes wrong */
  select
    t1.*,
    t2.bill_complete_dt
  from my.file2 t1 left join my.file1 t2
  on t1.key = t2.key
;
quit;

[current_thing]

thank you Kurt! this worked 😀

[RichardDeVen]

In some scenarios you will want to perform two atomic operations on an existing data set (or remote data base table)

• add a column to the table (alter)
• populate the column (update)

Example:

data one;
  input key a b c;
  datalines;
1 10 20 30
2 20 30 40
3 30 40 50
;

data two;
  input key (p q r) (3*:$1.);
  datalines;
1 A B C
2 D E F
3 G H I
4 J K L
5 M N P
;

proc sql;
  alter table two modify c num;
  update two
    set c = (select c from one where one.key=two.key)
  where
    exists  (select * from one where one.key=two.key)
  ;

[current_thing]

thank you! can I use alias for the tables to join the key? what's the syntax?