How to insert records from other tables to a table with more columns in sas

0
votes

I have two sas tables, A and B, A has two columns (i.e., columna columnb) and table B has four columns (i.e., columna columnb columnc columnd ), I wish to insert records from table A to table B, I tried the following, but it shows me errors:

PROC SQL;
  insert into B
   select *, columnc='a', columnd='b' from A;
QUIT;
2
sas
What are the errors? – Stu Sztukowski
You cannot compare the string 'a' to the variable columnc if columnc does not exist in table A. – Tom
@Tom OP is not trying to compare, I don't think; OP is trying to insert 'a' into columnc and 'b' into columnd. – Joe

2 Answers

2
votes

Assuming you just want to leave the extra columns empty then don't include them in the insert. It is much easier to just use SAS code instead of SQL code.

proc append base=b data=a force nowarn;
run;

For the SQL Insert statement you need to specify which columns in the target table you are writing into, otherwise it assumes you will specify values for all of them.

insert into B (columna,columnb)
  select columna,columnb
  from A 
;

If instead you want to fill the extra columns with constants then include the constants in the SELECT list. 

insert into B (columna,columnb,columnc,columnd)
  select columna,columnb,'a','b'
  from A 
;

If you are positive that you are providing the values in the right order then you can leave the column names off of the target table specification.

insert into B 
  select *,'a','b'
  from A 
;
0
votes

You can't specify the variable name that way; in fact, you can't specify the variable at all using insert into. See this example:

proc sql;
  create table class like sashelp.class;
  alter table class 
    add rownum numeric;
  alter table class
    add othcol numeric;
  insert into class
    select *, 1 as othcol, monotonic() as rownum from sashelp.class;
quit;

Here I use as to specify the column name, but notice that it doesn't actually work: it puts 1 in the rownum column, and the monotonic() value in othcol, since they're in that order on the table.