05-08-2012 01:42 PM
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?
05-08-2012 01:53 PM
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.
insert into class
select * from sashelp.class;
05-08-2012 02:25 PM
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.
05-08-2012 02:43 PM
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;
05-08-2012 03:03 PM
ok, I tested proc append and really seems a better alternative in my case.
Anyway PROC SQL seems to have some bugs.
05-09-2012 12:57 AM
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
05-12-2012 07:56 PM
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
where age EQ 12 ;
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) )') ;
Finally, look at the results:
select * from inserted_table ;
Name Sex Age Height Weight
James M 12 . .
Jane F 12 . .
John M 12 . .
Louise F 12 . .
Robert M 12 . .
10-09-2012 05:34 PM
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.