BookmarkSubscribeRSS Feed
tom12122
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?

7 REPLIES 7
Reeza
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;

run;

proc sql;

insert into class

select * from sashelp.class;

quit;

tom12122
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.

Ok.

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.

Reeza
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;

run;

tom12122
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.

Ksharp
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 ;
qui

Ksharp

Howles
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         .         .

droog
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4527 views
  • 0 likes
  • 5 in conversation