Desktop productivity for business analysts and programmers

EGuide Query Summary Function Gotcha

Reply
Valued Guide
Posts: 2,111

EGuide Query Summary Function Gotcha

Just to share a nasty surprise. I was using Query Builder to count the number of visits that had dates present The generated code was

(SUM(t1.SGVisitDate>.)) AS CHEvisitDone

When that code runs against a SAS dataset, all is well. When it runs against an Access table via a LIBNAME (to ACCESS, not to ODBC), it returns all NULLS (no errors or warnings).

If I change the summary function to
COUNT(t1.SGVisitDate) AS CHEvisitDone
I get the expected answer with either source.

Caveat emptor! I will also send to tech support.
Valued Guide
Posts: 2,111

Re: EGuide Query Summary Function Gotcha

Response from designers to tech support: "It's not a bug, it's a feature."

I finally found the behavior documented on page 31-32 of the "SAS/Access 9.2 for Relational Databases, Reference" 4th Ed.

Caveat emptor.
Ask a Question
Discussion stats
  • 1 reply
  • 104 views
  • 0 likes
  • 1 in conversation