LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (November 2003, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page