Proc SQL Aggregate/Group By Error

[FredGIII]

I have a proc sql code that I am connecting to a Postgresql database and pulling data from. I have simplified the code and trying it on its on, but when I submit it through Proc SQL, I get an error. When I submit the same code directly into Postgresql it works just fine. Any ideas?

The simplified code is :

proc SQL;
Connect to odbc(dsn ='database' uid =guest pwd =guest );
Select * from connection to odbc
(Select date(date_time)as iDate
,equipmentsernum
,AVG(xxx)
,AVG(yyy)

From "tablename"
Where date_time >= '2007-01-01'

Group By equipmentsernum, iDate
);
Disconnect from odbc;

Simplified - I am pulling daily averages for fields. I cast the timestamp date_time into just date and group by iDate. As mentioned, when I submit this directly to Postgresql, the code works, but when submitted through Proc SQL I get the following error:

ERROR: CLI describe error: ERROR: column "tablename.date_time" must appear in the GROUP BY clause or
be used in an aggregate function; No query has been executed with that handle

This is a normal error that occurs when you group by with aggregates - you need to
include all non-aggregates in the group by. However, I have included iDate in the group by and the query works directly.

Any suggestions would be appreciated.

Regards,

Fred

[Doc_Duke]

date_time and iDate are not the same variables. It should work if you change the WHERE clause to include iDate.

CLI is "Call Level interface" and basically is ODBC trying to understand the commands before sending them to Postgresql. Not all ODBC drivers are created equal and it appears that the one provided by Postgresql does not include all the richness of the native Postgresql SQL.

Doc Muhlbaier
Duke

[FredGIII]

Thanks for the reply. You are right that iDate and date_time are not the same, but it is not necessary for them to be the same as far as SQL is concerned. In the Select statement, I am casting date_time to date to pull just the date portion and so that aggregate calculations can be grouped by just the date (and not date_time). This way I get daily averages. The code works just fine in Postgresql.

As for CLI, why should it be trying to understand the commands? Isn't this just a pass through? I just want to pass the query through and return the results.

Thanks,

Fred

[Ksharp]

Hi.
I suggest that firstly create a table include all the variables(include iDate) ,then ' group by iDate' .


Ksharp

[darrylovia]

Maybe you could try to group by the column position instead of the name like this


proc SQL;
Connect to odbc(dsn ='database' uid =guest pwd =guest );
Select * from connection to odbc
(Select date(date_time)as iDate
,equipmentsernum
,AVG(xxx)
,AVG(yyy)

From "tablename"
Where date_time >= '2007-01-01'

Group By 2,1
);
Disconnect from odbc;

[FredGIII]

I tried :

Group By 2, 1 as you suggested. I was very hopeful but alas, I still get the same error:

ERROR: CLI describe error: ERROR: column "875037.date_time" must appear in the GROUP BY clause or
be used in an aggregate function; No query has been executed with that handle

I also tried performing an inner query to pull all the points and cast the date_time to date and then an outer query to group the items, but that just gave me a separate error. In all cases, the code runs with no errors in PostgreSQL, but I get the errors when I try to submit it from SAS Proc SQL. I have submitted a support ticket, but so far they haven't figured it out either. Just hoping someone out there has stumbled upon this before.

[DBailey]

I'm not sure standard sql syntax allows you to use the calculated column name in the grouping syntax. I'm pretty sure Oracle doesn't. I'm not sure if the odbc driver is doing any parsing or not. You could replace the iDate calculated name with the formula. If this doesn't work, I think you have other issues that are being masked somehow.


proc SQL;
Connect to odbc(dsn ='database' uid =guest pwd =guest );
Select * from connection to odbc
(Select date(date_time)as iDate
,equipmentsernum
,AVG(xxx)
,AVG(yyy)

From "tablename"
Where date_time >= '2007-01-01'

Group By equipmentsernum, date(date_time)
);
Disconnect from odbc;

[FredGIII]

> I'm not sure standard sql syntax allows you to use
> the calculated column name in the grouping syntax.
> I'm pretty sure Oracle doesn't. I'm not sure if the
> odbc driver is doing any parsing or not. You could
> replace the iDate calculated name with the formula.
> If this doesn't work, I think you have other issues
> that are being masked somehow.
>

The database that I am connecting to via SAS is a PostgreSQL database. All of the code that I have shown works without error when I submit it directly into PostgreSQL. And I have tried replacing the iDate calculated name with the formula in the GROUP BY clause and get the same error. What I find interesting is that it works perfectly in PostgreSQL but the exact same code gives me an error when submitted from SAS. I eliminated all group by and simplified my code just to see if the basice query was working and indeed it does. Btw, I commented out the iDate portion and then I got the same error with equipmentsernum even though equipmentsernum was clearly included in the GROUP BY.

It almost appears to me that the SAS odbc client doesn't handle group by with aggregation properly. It must be changing something, because a quick copy and paste of the SQL code runs just fine. The problem is that I really need to be able to submit from SAS because I have a higher level program that is looping through a large number of units and parsing the data.

[DBailey]

Well...I'm stumped.

[Ksharp]

Maybe you need this:

[pre]
proc sql;
select month(date) as iDate,avg(air) as mean_air
from sashelp.air
group by calculated iDate;
quit;
[/pre]


Ksharp

[FredGIII]

Solution - Tech Support came through - Thanks! The problem was due to comments. Trying to be a "good" designer and comment my code, apparently causes problems when submitting SQL code through ODBC. PostgreSQL allows for the standard /* comment */ or the use of --comment. The double dash comment was causing the problem. Removed them and voila! It worked, whew!

[DBailey]

ummm....so the example we all were working on wasn't what you were actually submitting?

[FredGIII]

> ummm....so the example we all were working on wasn't
> what you were actually submitting?

Unfortunately this is true and for that I apologize. On my defense though, I never suspected comments as being the culprit. The comment format is perfectly legal in SQL and ran perfectly when submitted directly to PostgreSQL. Therefore I didn't include them in the example. Additionally, the error message was pointing to a grouping variable which in hindsight is very misleading.

The lesson I learned is not to take anything for granted and submit everything for inspection and review.

[DBailey]

I, for one, would have immediately jumped on the presence of the double dash comment symbol as the culprit..........ok...maybe not.