topic ERROR: The following columns were not found in the contributing tables in SAS Data Management

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

Nehcour0420 — Tue, 14 Aug 2012 19:50:02 GMT

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

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

Reeza — Tue, 14 Aug 2012 20:03:21 GMT

Can you show the log with the error in it?

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

LinusH — Wed, 15 Aug 2012 07:45:39 GMT

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.

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

Patrick — Wed, 15 Aug 2012 10:26:21 GMT

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;

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

LinusH — Wed, 15 Aug 2012 10:36:52 GMT

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