topic Re: Where is SAS enterprise "VIEW" stored in SAS Enterprise Guide

Where is SAS enterprise "VIEW" stored

PadraigODea — Tue, 14 Feb 2017 15:36:05 GMT

Hi,

I have created a "View" in SAS Enterprise. (My first!)

If I open a brand new project then I expect to be able to create a query based on that "view" created earlier.

QUESTION: Where will I find the view, what library, what server? etc?

And yes, I do have an understanding that a "view" is not real data - merely a view.

Re: Where is SAS enterprise "VIEW" stored

ballardw — Tue, 14 Feb 2017 16:02:57 GMT

You should have assigned the view to a library just like a dataset. The instructions for building the view results reside in that library. If you did not specify then it was very likely placed in the WORK library. Which means that it was likely cleaned up when you ended your SAS session and that you likely need ot re-do the work and place the view in a permanent library to avoid recreating it every time you need it.

Re: Where is SAS enterprise "VIEW" stored

PadraigODea — Tue, 14 Feb 2017 16:24:47 GMT

Thanks,

I understand - almost!

I right-clicked on the query that creates the view and then click "properties" then click "results".

In the "results" I changed the output folder and filename to an existing library.

However, now when I run the query I get the following error;

"ERROR: The CREATE VIEW statement cannot be processed directly by the database.

ERROR: Create View failed."

mmmm...I am not sure what to do now??
All help appreciated.

Re: Where is SAS enterprise "VIEW" stored

ballardw — Tue, 14 Feb 2017 18:09:58 GMT

Are creating that in a library that represents an EXTERNAL database? I'm not sure how that would work. I only use SAS views associated with SAS data sets.

Re: Where is SAS enterprise "VIEW" stored

LinusH — Tue, 14 Feb 2017 23:15:47 GMT

You can definitely create views pointing to RDBMS data. SAS is really a great tool for working almost seamless with different data sources.
But the key is that you can't create a SAS view and store in a RDBMS libref. Store it in a SAS libref.
If you think it's better to store it in the RDBMS you need to create it using the target RDBMS SQL syntax either directly in the database or in a PROC SQL explicit pass through step.

Re: Where is SAS enterprise "VIEW" stored

PadraigODea — Fri, 17 Feb 2017 10:21:50 GMT

Thanks for feedback on my "view" question.

I have been out of office for a couple of days.

Allow me clarify the question.

My data is static historic data with zero updates/additions.

The structure is not friendly as sometimes data is split over 5 tables when it should all be in the same table. This makes simple queries more complex (appending 5 tables).

Question:

Option A : Do I I create ONE new table which hold all the data from the 5 tables (duplicated)
or

Option B: Do I create a view of the 5 tables

The number of records is fairly large ... which would make the option "B" "view" slower than if I created a new table?
Or is it breaking every rule in the book to create a new table?

Re: Where is SAS enterprise "VIEW" stored

LinusH — Sat, 18 Feb 2017 07:00:47 GMT

Views that append data usually performs well. When it comes to joining it's a different story.
On the other hand, a new table can be optimised by storing it on the most used dort order and applying indexes to it. If the data is static you could even remove the original tables if storage is a concern.