Date: Tue, 4 Nov 2003 17:08:49 -0500
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: Cross-Table with MS Query via ODBC
Content-Type: text/plain
Werner:
From what you have tested, I gather that, since you have an ODBC data source
defined for your SAS library SASHLP2, this inner query should work OK in MS
Query:
SELECT AVG(CLASS.Age) AS AVGAge
FROM SASHLP2.CLASS CLASS
GROUP BY 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
-----Original Message-----
From: Werner Prystav [mailto:wnews@DLAND.DE]
Sent: Tuesday, November 04, 2003 3:09 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Cross-Table with MS Query via ODBC
Hi Sigurd,
I am sure the names are allright. Please read my answer to Hamani and have a
second look to the code that was created by MS Query and works in MS Query.
There is a two-level-name in the FROM clause.
Werner
"Sigurd Hermansen" wrote:
> Werner:
> The query,
>
> TRANSFORM Avg(SASTAB.Value) AS [Mittelwert von Value]
> SELECT SASTAB.fix
> FROM SASLIB.SASTAB
> GROUP BY SASTAB.fix
> PIVOT SASTAB.Descr;
>
> mixes elements of MS queries and SAS SQL queries. In particular, the
> reference in the FROM clause (SASLIB.SASTAB) has the SAS two-level
> <library>.<memname> form instead of the SASLIB_SASTAB form in one of
> the prior examples. Also, if you want to use SASTAB as a table name
> alias, you will need to define it (FROM SASLIB_SASTAB SASTAB). Sig
>
> -----Original Message-----
> From: Werner Prystav [mailto:wnews@DLAND.DE]
> ...
> Excel starts MS Query. The Query SELECT ... (without TRANFORM PIVOT)
> buildt by MS Query works fine:
>
> SELECT SASTAB_0.fix, SASTAB_0.Descr, SASTAB_0.Value
> FROM SASLIB.SASTAB SASTAB_0
| 
