Help using Base SAS procedures

Proc SQL: difference between Table and View

Reply
Occasional Contributor
Posts: 19

Proc SQL: difference between Table and View

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
Super User
Posts: 17,837

Re: Proc SQL: difference between Table and View

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.
Trusted Advisor
Posts: 2,113

Re: Proc SQL: difference between Table and View

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
Super User
Posts: 9,681

Re: Proc SQL: difference between Table and View

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
New Contributor
Posts: 2

Re: Proc SQL: difference between Table and View

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.

Super User
Posts: 5,257

Re: Proc SQL: difference between Table and View

For SQL views:

describe view libname.viewname;

For data step views, use the describe statement.

For details, see online doc,

/Linus

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 2100 views
  • 0 likes
  • 6 in conversation