topic Re: SAS SQL Tables vs Dataset in SAS Procedures

SAS SQL Tables vs Dataset

metalray — Mon, 17 May 2010 15:00:48 GMT

Hello,

I am very new to SAS and just go through the SQL training and did not quite get the difference between tables or views created through SAS SQL "Create TABLE" and data sets/files created in the data step that holds actual data, or a SAS data view, a data set that references data that is stored elsewhere.

Which ones would you use when (situation)...?

Thanks for clarification

Re: SAS SQL Tables vs Dataset

Doc_Duke — Mon, 17 May 2010 15:55:05 GMT

CREATE TABLE creates the same SAS dataset that a DATA step might. Ditto the views.

Which to use is often a function of other parts of your computing environment and whether your data are dynamic or static. Views take longer to process, but use less disk space. A view is updated whenever the underlying table is updated, so you don't have to re-run some program.

Re: SAS SQL Tables vs Dataset

metalray — Tue, 18 May 2010 08:41:09 GMT

I see. Thanks for explaining. In SAS SQL the "CREATE TABLE" does create a dataset.

I assume that the dataset creation is helpful when using MS excel as a source or text files, while CREATE TABLE can be used to replicate data as a SAS dataset that can be retrieved from relational data sources.

why would one replicate data as a SAS dataset when it is already in relationsl data sources?

Re: SAS SQL Tables vs Dataset

Patrick — Tue, 18 May 2010 09:39:13 GMT

"why would one replicate data as a SAS dataset when it is already in relationsl data sources?"

It's most often about data aggregation or denormalisation and not simple replication. Whether to store such aggregated data/a data mart as a table or not depends most of the time whether it is used more than once (storage requirements vs. performance).

About views: Think of a SQL view as nothing else than hidden SQL code which gets executed whenever you access the view (the same is also true for a SAS data step view).

I.e:

proc sql;
describe view sashelp.vstable;
quit;
will show you the SQL code executed when you access the view sashelp.vstable

data view=....
describe;
run;
shows you the data step view.

When the SQL query takes a long time and the same data is accessed more than once then it might be better to create a intermediary table instead of a view (because whenever you access the view the query has to be executed).

HTH
Patrick

Re: SAS SQL Tables vs Dataset

metalray — Tue, 18 May 2010 13:35:50 GMT

Thanks a lot to both of you for the explanation.
I do have a strong Hyperion OLAP background and as a
staging database we used a relational Oracle DB and did all
the transformation there but I do still not get the incentive
of using the SAS data set to do so. For me, admittedly as a SAS beginner, I
do think that a database such as Oracle is better to keep data
than a SAS data set and I assume with tools such as SAS Access I get query a relational database or SAS OLAP cube to do the reporting.

Re: SAS SQL Tables vs Dataset

Patrick — Tue, 18 May 2010 14:27:43 GMT

Agree with what you say.

What people often do is reduce the amount of data within the DB, then pull it to SAS, do may be a bit further processing (it's just often easier to code with SAS than with SQL only) and then use the SAS Proc's.

Also in case where the data source comes from more than one system/DB one has to decide where to do the joins.

Re: SAS SQL Tables vs Dataset

Howles — Tue, 18 May 2010 21:34:03 GMT

> CREATE TABLE creates the same SAS dataset that a DATA
> step might. Ditto the views.

Not quite ditto for the views. They are interoperable as data sources, but can differ in terms of other functionality. For example, the DESCRIBE statement in PROC SQL will choke on a DATA step view, and the DESCRIBE statement in a DATA step will choke on a PROC SQL view. For example, PROC SQL views can be updated but DATA step views cannot. See http://www.howles.com/sqlbook/ (Chapter 10) for examples.

Re: SAS SQL Tables vs Dataset

metalray — Fri, 11 Jun 2010 12:15:58 GMT

@Patrick,

I am still restless about this issue. I dont want
to unsettle anyone here but right now I (admittedly a SAS beginner coming from
the Hyperion camp) cant see the point of loading my 30 GB tables
from DB2 and Oracle into SAS data sets to then get them into the SAS Olap
cube. in theory I would create a solid and multiplexed oracle db
that is accessed with SAS/Access from SAS Cube Studio.

To me, a staging areas with SAS data sets, is in competition with a full fledged staging area in Oracle and somehow I see the SAS data sets as inferior…so why do they exist?

PS: PLSQL is quite powerful for data manipulation as well.

Re: SAS SQL Tables vs Dataset

deleted_user — Fri, 11 Jun 2010 13:20:39 GMT

how about using sas views using "create view" to access oracle db.

Re: SAS SQL Tables vs Dataset

metalray — Fri, 11 Jun 2010 13:44:56 GMT

I can just use Oracle Views with SAS/Access, with Oracle indexes also used by other Oracle DB Objects and I have it all nicely in one box. Where is the need for SAS Datasets or Views here?

Dont get me wrong. I think SAS has great analytic capabilities but I just dont get the need to use its data formats.

Re: SAS SQL Tables vs Dataset

Bill — Fri, 11 Jun 2010 17:52:11 GMT

metalray;

To use a SAS PROCedure, the data has to be in a SAS dataset or SAS view so that the procedure can use it. Data that resides in a DB somewhere is rarely ready to use in any procedure and certainly cannot take advantage of the rich SAS formats and functions.

Re: SAS SQL Tables vs Dataset

Paul_Kent_SAS — Fri, 11 Jun 2010 20:46:57 GMT

Hello metalray; you might not need SAS datasets/views - quite possibly you are luckier than some/most folks in that all the data you need are in the database and/or you are permitted to upload the data you want into the database (to enrich that which is allready there)...

many times folks use SAS datasets to join data from the database to that from elsewhere, or to reshape data from the database... their local DBA conditions are such that they cant do it in the database, so they do it with SAS databases...

if your data are ready for the analytics without any transformation, you are in good shape to feed it into the SAS procs with a simple libname statement

paul

Re: SAS SQL Tables vs Dataset

metalray — Mon, 14 Jun 2010 07:44:03 GMT

Thanks for the feedback guys. That helped.

Re: SAS SQL Tables vs Dataset

Peter_C — Mon, 14 Jun 2010 11:07:05 GMT

"metalray"

for "in-database enabled" SAS procedures
which move the sas analytics (intellectual property) into the database execution platform (rather than moving the data out of the data base into a platform where SAS executes ) see "Paper 300-2010 SAS® Presents In-Database Base Procedures in Practice" at
http://support.sas.com/resources/papers/proceedings10/300-2010.pdf

(imho) it is a major (and now obvious) strategy for analytic performance.

peterC