Help using Base SAS procedures

PROC SQL - same column names problem - SAS tries to be too smart

Reply
Contributor
Posts: 60

PROC SQL - same column names problem - SAS tries to be too smart

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
Posts: 19,822

Re: PROC SQL - same column names problem - SAS tries to be too smart

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;

Contributor
Posts: 60

Re: PROC SQL - same column names problem - SAS tries to be too smart

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.

Super User
Posts: 19,822

Re: PROC SQL - same column names problem - SAS tries to be too smart

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;

Contributor
Posts: 60

Re: PROC SQL - same column names problem - SAS tries to be too smart

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

Anyway PROC SQL seems to have some bugs.

Super User
Posts: 10,035

Re: PROC SQL - same column names problem - SAS tries to be too smart

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

Regular Contributor
Posts: 184

Re: PROC SQL - same column names problem - SAS tries to be too smart

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

Occasional Contributor
Posts: 7

Re: PROC SQL - same column names problem - SAS tries to be too smart

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.

Ask a Question
Discussion stats
  • 7 replies
  • 2433 views
  • 0 likes
  • 5 in conversation