BookmarkSubscribeRSS Feed
Obsidian | Level 7

I need to insert into one table values from another. The problem is that  table from which i'm iserting values can change and contain different set of columns in different order (but all of them for sure exist in the table I'm inserting into).

So I made a macro variable (c) with set of columns that are being inserted (separated with "," eg. "col1,col2,col3") and use:

insert into inserted_table (&c) 

select &c from work.source_table s;

When I execute it as script everything is ok.

When I put it inside marco and execute it from call execute (I need to execute it many times during which &c changes - don't ask why) i get error:

ERROR: INSERT statement does not permit correlation with the table being inserted into.

ERROR: The reference to col1 appears to refer to a variable in the table being inserted into.

I don't know why SAS doesn't from default get columns from proper table and tries to be smarter than god. All columns from select statement (source_table) exist in table (inserted_table) and from SQL syntax is clear what should be done.

I don't want to put table alias into macro variable as I don't want to complicate more code. It seems as bug and want to deal with it.

Can you help me?

Super User

What does your macro code look like fully?  And where you call it, ie your call execute statement.

You don't need to specify the columns in your first line either, just make sure that the table being inserted has the columns in the same order as the table being appended to, which is actually a SQL limitation, not SAS.

Proc append is more efficient though, so you may want to consider that.

data class;

    set sashelp.class;


proc sql;

insert into class

select * from sashelp.class;


Obsidian | Level 7

the problem is that my column names may be in different order and I want to be independent of their order (an morover some may be missing - I have different combinations of them). That's why I specify columns in insert into () statement.


I have allcomb function which generates combinations of values that are coherent with column names.

I use them to get proper data for optimization. Then I get the same column names as results from otimization.

I run that optimization for every combination and need to put them in proper place in results table.

That's why i want just to point columns and be independent of their order.

When I run macro with proc sql inside everything is ok. When I call then same macro from call execute (i need to because I need to call it many times with different parameters) sas returns an ERROR.

Super User

INSERT for SQL requires the columns to be in order or specified in the first line (like you've done)

When I worked with T-SQL (MS SQL) it was the same, requirement of the language, not a SAS restriction.

If you're missing variables I'm assuming you're specifying null/missing appropriately?

Does the code work for all your iterations and then not work for a specific one or doesn't run when you call it. You still haven't shown any examples of how you call it so can't help there.

Proc Append is independent of the variable order and if a variable is missing it just add the new records with that variable missing.

proc append base=my_data data=new_data force;


Obsidian | Level 7

ok, I tested proc append and really seems a better alternative in my case.

Anyway PROC SQL seems to have some bugs.

Super User

Maybe it is a stupid method. But you can use keyword corr to require SQL merge it according to the variable name not its position .

proc sql;
create table want as
 select * from a
 outer union  corresponding
 select * from have ;


Quartz | Level 8

Seems to work as expected for me.

First create test tables:

proc sql ;

create table inserted_table like sashelp.class ;

create table source_table as

select age, height, name, sex

from sashelp.class

where age EQ 12 ;

quit ;

Now define macro:

%macro do_insertions(c=) ;

   insert into inserted_table(&c)

   select &c from work.source_table s ;

%mend do_insertions ;

Next use CALL EXECUTE to trigger the macro:

data _null_ ;

if _n_ EQ 1 then call execute('proc sql ;') ;

call execute('%do_insertions( c=%str(name, sex, age) )') ;

run ;

Finally, look at the results:

select * from inserted_table ;

quit ;

Should see:

Name      Sex       Age    Height    Weight


James     M          12         .         .

Jane      F          12         .         .

John      M          12         .         .

Louise    F          12         .         .

Robert    M          12         .         .

Calcite | Level 5

I've had this issue when the source table did NOT have all the columns I expected, ie. one required for the INSERT to the target table.  Check your source table and make sure it has all the columns you expect.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5 in conversation