When you are constantly taking the data tables and completing joins to begin working on your reports or analysis it might be time to consider creating permanent views. Then you can just add the view to the Enterprise Guide project rather than dealing with the joins in a Query Builder task.
There are two options to do this off of your currently built join in Query Builder.
- Modify the options to save the table in a permanent library. (In the example, I saved to a library called Storage.) Then update the preferences to save as a VIEW instead of a data TABLE.
- Use the preview screen to grab the existing PROC SQL code and then paste that into a SAS code node to make modifications.
Included below is a quick movie to see these two steps in action.
I have two dimension tables (Customer and Product) that I have to constantly merge into the sales_history table. Saving this as a permanent view (storage.candy_fulltable) means that I can just use that next time instead of rebuilding this join.
10 Comments
Hi Chris,
To come back to Ben's original question/issue of creating DBMS views through ACCESS engines (http://support.sas.com/kb/12/809.html). The lack of this capability is really hurting 'cause it makes it hard to use Enterprise Guide in a pure DBMS environment, for example a typical use case would be to use Query Builder to join and manipulate DBMS tables with the result being put directly into another DBMS table, completely running in-database, no data going out-of-db. Also the fact that Enterprise Guide Query Builder lacks proper capabilities to produce PROC SQL ... EXECUTE ... pass-through for creating a DBMS view is very disappointing. I believe this is still the case in EG 5.1.
IMO if EG would have these capabilities it would open it to a completely new audience that due to their specific environment have to and want to work only in-database.
Thanks,
Nicolas
Nicolas,
In EG 5.1 with Query Templates, you also have the ability to use a template as a subquery. For *some* scenarios that helps to keep the work in database, as you can then add a "subselect" to a SELECT operation without moving any data to a transient table.
It doesn't help all cases, and it doesn't use EXECUTE, but it's something you might want to investigate. See this comprehensive conference paper for details.
Wow ... very useful especially when you find yourself constantly creating demos or examples to give to others! I sometimes forget which SASHELP and SAS EG Help tables go together. (If you are looking for sample data here's some resources I have found. Let me know of any others - although the UFO sightings data is my favorite so far.)
Now I gotta go look at what Chris is talking about ... EG is getting so smart (and helpful).
If you have SAS Enterprise Guide 5.1, another way that you can save your Query work is to use Query Templates. You can now take a query definition and save as a task template, which you can then drop into any other project that you want.
I am a big fan of using views however I have found that it can be more effective to create them directly on the database you are using rather then a SAS view. Unfortunately at least with my current version of SAS I was not able to create a view using the query builder in an Oracle library. Have you heard any news about that being added to the ACCESS engine?
Hi Ben! Thanks for the comment. Depending on your authorization within Oracle, you should be able to create views from the Access Engine via libname statements.
I don't have the ability to test that this morning, but I have found similar examples from a search within LexJansen.com site such as: http://www2.sas.com/proceedings/forum2007/239-2007.pdf (Also according to the User Documentation on pg 75, this is possible.)
I'm wondering if you are getting an error similar to:
"ERROR: You cannot create or delete datasets, views or indexes in this mode. Try the option METAOUT=DATA. Use Proc Metalib to create metadata for datasets."
This is related to your access level. Check out this document on managing metadata libraries and metadata security for Enterprise Guide users.
http://support.sas.com/documentation/onlinedoc/guide/EG43MetaLibraries.pdf
Ben: With big tables, SAS views can be very slow: SAS 9.2 seems to always pull down the entire view from Microsoft SQL Server, and then perform any filtering locally. Maybe that is what you meant? For this reason, I rarely use SAS views. You should be able to create a server-side view using a pass-through connection.
Thanks Andrew. The Access Engines can translate a large group of functions into the native RDBMS however when using SAS functions outside of this list, SAS will then need to pull down all the data and run the function on the SAS side. This is definitely more cumbersome than creating views directly on the RDBMS. The ACCESS engine User Documentation has sections starting with 'Passing SAS Functions to' from each RDBMS supported. Verify that the functions being used are included in the access engine so they can be translated appropriately. Here is the link to Passing SAS Functions to Oracle, as an example.
Note that from SAS EG, you can build views onto the RDBMS. In my movie example, the library STORAGE was a base SAS libref, however you could point to an Oracle, Teradata, SQL, etc library and create a view directly there.
Hello,
I am having the same problem being not able create a view in Oracle using query builder. Did you find a solution for that?
Thanks and regards,
Baslan.
I was never able to get it to work other the using explicit passthrough. Here is the related track response.
Benjamin,
You are correct, you can create an Oracle table but not an Oracle view with the LIBNAME engine:
http://support.sas.com/kb/12809
I can't find any plans to support the creation of Oracle views with the LIBNAME method in the future.