Proc SQL Log messages a debugging concern/inconvenience?

[novinosrin]

Hello Folks,  You may or may not have noticed that a lot of times Proc SQL log messages such as ERRORS(dangerously)/WARNINGS seems to throw rather vague and misleading messages. 

Taking a simple example, let us suppose I have a dataset and I want to create a variable that should have values of 1/0 for values higher than avg.

data have;
 do i=1 to 10;
  output;
 end;
run;

proc sql;
create table want as
select *,avg(i) as avg, i>avg as foo
from have;
quit;

ERROR: Expression using greater than (>) has components that are of different data types.
ERROR: The following columns were not found in the contributing tables: avg.

If you notice the 1st error, you would wonder how on earth that can be the message. Yeah right, the second one is obvious. In essence, the above example is rather trivial and most certainly for SAS pro's as you know all that fixes the above is the missing CALCULATED reference to the computed column in the SELECT clause.

proc sql;
create table want as
select *,avg(i) as avg, i>calculated avg as foo
from have;
quit;

Fair enough!, What if the same occurred in a lot of sub-queries and to make the matters worse what if it occurred in a combination of CORRELATED subsidiaries, JOINS and IN-Line views adding some spice with an expression in GROUP BY after all. It seems by the time we figure, half of us would prolly need some treatment for mental or some other eye-related condition. 

Any thoughts?

[ChrisHemedinger · Accepted solution]

The particular flavor of SQL that you find in PROC SQL craves specificity, and abhors ambiguity.  The CALCULATED keyword tells SAS exactly "what is this avg you're referring to".  I've learned to err on the side of reducing ambiguity by using the CALCULATED keyword whenever referencing a calculated field, and using table aliases whenever referencing more than one table (even if there is no overlap/conflict).

If I see a headscratcher log message, I double-check to make sure I've coded for minimal ambiguity.

[PGStats]

Instead of

"has components that are of different data types."

SAS/SQL should simply say

"has unknown components."

The "different data types" message should be reserved for cases where the data types are known.

I suspect that this message comes from a software layer where the details of the underlying problem are unknown. So maybe the message should read:

"has unknown components or components that are of different data types."

[ballardw]

I will agree that any such error involving one or more variables should at least list one of the variables. I strongly suspect that the parser/compiler generating the error code has to know which variable is being looked at when the error flag is set.

[novinosrin]

Good morning All, Thank you for participating in the thread and for offering your opinions/thoughts etc. The reason I delayed in closing the thread was on purpose to actually receive and share a feedback from my colleagues at Citizens and my DePaul college mates who follow me. Jeez, scary to think we're all getting old to acknowledge being alumnis now

Okay, So basically, there are few for certain/perhaps many instances where Proc SQL log messages particularly Error/Warning messages are not quite right. One that is discussed here data types conversion, and other for example ORDER By notes and some have even noticed in place of CASE WHEN vs IFN etc. If one has to really dig deep and collect , there may be more.

Honestly, all 3 of you are not best examples to feel deterred as you are all highly experienced and for you this would seem very trivial. But for a starter, or for somebody whose background is RDBMS background and so forth, I am afraid when they are in pressure to deliver something at breakneck speed, a slight miss is basically falling off the cliff. 

So it's apparent the conclusion for now is to code correctly and accurately upfront. 🙂