BookmarkSubscribeRSS Feed
Nehcour0420
Calcite | Level 5

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

4 REPLIES 4
Reeza
Super User

Can you show the log with the error in it?

LinusH
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 4666 views
  • 0 likes
  • 4 in conversation