BookmarkSubscribeRSS Feed

We currently face the problem that when we query SQL Server via Enterprise Guide 7.1 Query Designer, if we design a query that calculates the mean of an int column, the result is also an int. For a non-IT user who does not know how SQL works this is a plainly wrong result and leads to errors in the resulting data analysis. 

 

SAS Support (call [SAS 7612275697]) said the only workaround for this issue is to switch off pass-through of SQL code to the database in the libname statement using "direct_sql = none". This results in a correctly calculated mean, but decreases performance of many queries unbearably. 

 

I hereby suggest to include a checkbox "disable implicit SQL passthrough" in SAS Enterprise Guide Query Designer. If checked, the proc sql option "NoIPassThru" is set in the proc sql code generated by query designer. This creates correct results with the price of sometimes extreme loss of performance. 

 

Alternatively, and even better, Enterprise Guide could be extended so that it 'understands' the problems of calculating the mean of an int column in SQL and automagically casts the int value to e.g. float (see e.g. https://stackoverflow.com/a/11106209) before applying the mean function, so that we get both fast execution (by the database) and correct results. SAS Support, however, mentioned that this is probably not doable for unstated reasons. 

 

Thank you for looking into this problem!

J.

4 Comments
Quentin
Super User

That's a great gotcha.  For those of us SAS types who aren't used to different types of numerics, it's indeed surprising that in MS SQL server, the mean of an int is an int. https://docs.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql

 

According to a note on stack overflow, the type returned by AVG() is not defined by the ANSI standard, so different databases do different things.  Sigh.

BeverlyBrown
Community Manager
Status changed to: Under Consideration
 
BeverlyBrown
Community Manager
Status changed to: Open
 
BeverlyBrown
Community Manager
Status changed to: Under Consideration