SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Contributor
Posts: 25

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

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

Super User
Posts: 17,868

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

Can you show the log with the error in it?

Super User
Posts: 5,257

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

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.

Data never sleeps
Respected Advisor
Posts: 3,896

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

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;  

Super User
Posts: 5,257

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

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

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

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 2962 views
  • 0 likes
  • 4 in conversation