Date: Tue, 26 Oct 2004 00:03:35 +0100
Reply-To: Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK>
Subject: Re: are character variables always stored in fixed length ?
In-Reply-To: <417D76B1.1050904@free.fr>
Content-Type: text/plain; charset="us-ascii"
Diana [mailto:diana.chirac@free.fr] (her message can be found below my sig.)
Seeks further guidance on alternatives to transferring her data warehouse on
an rdbms, into SAS tables.
For the situation you seem to be describing, SAS SQL views might be best.
They might not be needed, because SAS9 Access to rdbms allows rdbms tables
to be used like sas tables.
Here is a quote from SAS onLineDoc
"The LIBNAME statement enables you to assign SAS librefs to DBMS objects
such as schemas and databases. After a database is associated with a libref,
you can use a SAS two-level name to specify any table or view in the
database and then work with the table or view as you would with a SAS data
set."
See
http://support.sas.com/onlinedoc/912/getDoc/acreldb.hlp/a001370560.htm
Pass-thru views allow you a more "rdbms-specific" level of interaction, but
may come at a cost of more sophistication than you need.
Many datawarehouses on rdbms are optimised for collecting data rather than
analysing. Yours may be different. I should not generalise. You should
discuss query performance for the crunching you need, with your rdbms
support, and SAS Customer support.
On data storage lengths: I saw one rdbms implementation( name withheld to
protect me as well as that large provider ) where, for optimisation (for
development/maintenance or exeution was never clarified) most short
character fields had far more storage space than they needed. Perhaps that
platform used some clever variable length storage.
I hope your experience will be better.
Good Luck
Peter Crawford
Crawford Software Consultancy Limited
-----Original Message-----
From: diana [mailto:diana.chirac@free.fr]
Sent: 25 October 2004 22:57
To: Peter Crawford
Subject: Re: are character variables always stored in fixed length ?
Peter Crawford wrote:
> SAS compression comes in two forms, "character" and "binary". The
> "character" form is the more effective, if you seek only to reduce the
> I/O required, without too much cpu overhead.
>
> While seeking a long-term solution for holding rdbms data in sas,
> consider keeping only data views in sas, as proxies for the underlying
> data which could remain in the rdbms. If the data must be transferred
> to be stored in sas ( perhaps to limit the demands on the rdbms server
> for analytical processing ) then look carefully to keep the column
> widths defined in sas to the sensible minimum.
Thank you very much for this hint, using views sounds interesting, I'd like
hearing more about that: on crunching phase, is that really usable or rather
too slow ?
> Some rdbms may not offer the range of widths for character data
> offered by SAS.
I can't figure out what that means exactly.
diana
