ERROR: The following columns were not found in the contributing tables

[Nehcour0420]

Hi All,

When I was trying to do something like:

proc sql;
create table work.test as
select col1,
          col2,
          put(col3, 3.) as new_col3,
           sum(col4) as new_col4
from source_table
group by col1,
              col2,
              col3;
quit; 

I constantly got an Error: The following columns were not found in the contributing tables: new_col3

I did some search and understood that this kind of error usually appears when using some calculated value in the WHERE clause and can be solved by using the key word CALCULATED.

I am not sure if here's the same situation when I was trying to use GROUP BY.

If it is a similar situation, where should I use the CALCULATED key word?

Thanks!

Eric

[Reeza]

Can you show the log with the error in it?

[LinusH]

I don't think you can group by using a column that does not exits in the select clause. You should probably use new_col3 in the group by clause, no needfor the calculated keyword.

[Patrick]

Your SQL syntax as such seems to be o.k. when used within SAS SQL (below code works). Are you sure the error is thrown by this SQL and not somewhere "downstream"?

I believe what Linus has in mind is how SQL behaves on some data bases like Oracle. I'm always having "fun" when coding a group by there.

data sample;
  col4=1;
  do col1=1 to 2;
    do col2=10 to 12;
      do col3=100 to 102;
        output;
        output;
      end;
    end;
  end;
run;

proc sql; 
  create table work.test as 
  select col1, 
            col2, 
            put(col3, 3.) as new_col3, 
            sum(col4) as new_col4 
  from sample 
  group by col1, col2, col3; 
quit;  

[LinusH]

Actually, I didn't have that in mind, but it was polite of you to put it that way...

I was jumping to the conclusion without testing...:smileyblush:, butI learned something too