topic Proc SQL: difference between Table and View in SAS Procedures

Proc SQL: difference between Table and View

HDSimo — Thu, 24 Feb 2011 11:18:33 GMT

Hi,
I have some doubts about tables & views.
If i have to create some DS in order to manipulate data and then generate a final dataset, which kind of intermediate file is better to use, view or table? I know that views are smaller, what about the difference in the elaboration time?

Thank you

Simone

Re: Proc SQL: difference between Table and View

Reeza — Thu, 24 Feb 2011 18:41:59 GMT

My opinion only...
I use views for reports/datasets where a dataset will be updated and I want this other data set to be updated to reflect this.

A view is a step that is run every time it is accessed, rather than just reading a table. The speed depends on the calculations in the view.

I also have to balance the length of time the view takes to load and how often it is used. For example if a view takes 2 seconds to run but is run 100 times a day, I'd rather have a table updated. On the other hand if it takes 30sec to run, but is run ten times a month, perhaps a view is fine.

Another consideration to use a view over a dataset is storage space, but this only becomes an issue if you continue to use a lot of views, then you can 're-use' space. You still need the space to complete the task in the view.

Not sure I answered your question but hope it helps.

Re: Proc SQL: difference between Table and View

Doc_Duke — Thu, 24 Feb 2011 18:45:23 GMT

It's similar to the tradeoffs of datasets and views described in the base language reference (Chapter 29). It is basically a three-way balance between timeliness (view changes with underlying data), disk space (view smaller), and processing time (view generally slower). A view into a relational database may also be considered more secure as the DBA can apply some rules that might not be available in the file system for a table.

Message was edited by: Doc@Duke

Re: Proc SQL: difference between Table and View

Ksharp — Fri, 25 Feb 2011 01:25:32 GMT

Hi.
Table contains real substantive data,But View only contains information about how you can find data (such as some sql code). So its size will be smaller.

Ksharp

Re: Proc SQL: difference between Table and View

ab_UoN — Thu, 01 Sep 2011 02:10:10 GMT

Hi,

Is there a simple way to see the definition of the view. I have a view of a table and am trying to confirm the physical and metadta location of the table that feeds the view.

Re: Proc SQL: difference between Table and View

LinusH — Fri, 02 Sep 2011 11:29:59 GMT

For SQL views:

describe view libname.viewname;

For data step views, use the describe statement.

For details, see online doc,

/Linus