Date:         Wed, 5 Nov 2003 13:29:25 +0100
Reply-To:     Werner Prystav <wnews@DLAND.DE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Werner Prystav <wnews@DLAND.DE>
Subject:      Re: Cross-Table with MS Query via ODBC

Sigurd,

"Sigurd Hermansen" wrote:
    ...
> this inner query should work OK in MS Query:
>
> SELECT AVG(CLASS.Age) AS AVGAge
> FROM SASHLP2.CLASS CLASS
> GROUP BY CLASS.Age

Good idea. I wanted the AVG of Height (grouped by Sex and Age). The
following code provides such a table, but not a cross table.

SELECT CLASS.Sex, CLASS.Age, Avg(CLASS.Height) AS AVG_Height
FROM SASHLP2_CLASS AS CLASS
GROUP BY CLASS.Sex, CLASS.Age;
>
> If so, then SAS will can provide the necessary data for the TRANSFORM and
> PIVOT, and you'll have to determine why the  MS Query TRANSFORM and PIVOT
> clauses fail to recognize the yield of the inner query. That obviously
> becomes a question for another listserver.
> Sig

That is wrong, but you helped me to understand: SAS (respectivly the
SAS-ODBC-Driver) is not able to understand TRANSFORM and PIVOT. I had a
look in the OnlineDoc:


SAS ODBC Driver User's Guide and Programmer's Reference
Support for SQL Grammar
"Microsoft's ODBC specification defines three levels of support for SQL
grammar: MINIMUM, CORE and EXTENDED. The SAS ODBC driver supports all
of MINIMUM and some of the CORE SQL statements and the statement
elements. See the SAS Procedures Guide for complete information about
supported grammar."

In the SAS Procedures Guide I found no Statement TRANSFORM ... PIVOT
for PROC SQL. The SAS-ODBC-Driver does not know TRANSFORM ... PIVOT! MS
Access as well as the Access-ODBC-Driver accepts TRANSFORM ... PIVOT.
On the indirect way (MS Query queries Access via ODBC, Access queries
SAS via ODBC) SAS provides the whole table ("necessary data") to
Access. Access handles the TRANSFORM ... PIVOT query and creates the
cross table.

Was it a problem for the SAS listserver or an ODBC newsgroup or a SQL
newsgroup?

Werner