SAS/ACCESS: Universal SAS Methods to Access Just About Any Data, Anywhere
The recording is now available for my Ask the Expert presentation on May 29 on SAS/ACCESS: Universal Methods to Access Just About Any Data, Anywhere.
This Ask the Expert session illustrated the two main programming methods that can be applied with a consistent universal syntax for reading, writing and processing database tables: SASL pass-through and the SAS ACESS LIBNAME method. Examples of executing these methods were shown for several diverse data sources emphasizing the consistency of the SAS syntax, making it easy for SAS users to apply to any required database. Some performance considerations and efficiency techniques were also discussed.
Here below is a transcript for questions received during the session and following that there is a link to download the presentation slides and the SAS code that was used in the demonstration.
Which method is most efficient for transporting data to SAS: SQL pass-through or the LIBNAME statement?
Regardless of the method, when data is moved from the database and returned to SAS, it is done the same way regardless of the method. In either case the volume of data returned will determine how long the process takes.
How many SAS procedures are converted into the native database SQL when using SAS/ACCESS?
This table lists the BASE SAS and SAS/STAT procedures that convert part of the processing (sorting/computing summary statistics, altering data structure) in database before returning results to SAS.
|
Base |
SAS/STAT |
|
FREQ |
CORR |
|
MEANS |
CANCORR |
|
RANK |
DMDB |
|
REPORT |
DMINE |
|
SORT |
DMREG |
|
SUMMARY |
FACTOR |
|
TABULATE |
PRINCOMP |
|
|
REG |
|
|
SCORE |
|
|
TIMESERIES |
|
|
VARCLUS |
Additional SAS technologies do allow further SAS processing to occur in-database for Hadoop, Teradata, and Greenplum database systems, but these technologies do not generate native database SQL.
On the LIBNAME statement, I've always been informed that keeping that open, adds a lot of overhead. Is that correct? Or should we be closing as soon as done?
Yes, after you are finished, you should close the connection using:
libname mydbms clear;
The database administrators do not like to see open, idle connections.
Is there a SAS/ACCESS connection available for AWS S3? Either as pass-through or libname?
We have SAS/ACCESS Interface to Amazon Redshift:
see also:
https://communities.sas.com/t5/SAS-Communities-Library/Redshift-and-SAS/ta-p/361738
How do I know which SAS ACCESS engines/interfaces I have available with my current licensing?
You can submit the following program and view the output in the log to get a list of currently licensed products:
proc setinit noalias;
run;
When creating a DBMS table with SAS: sometimes it is more efficient to submit a create table with DDL, and then insert rows. SAS's interpretation is not always the most efficient. Also, when creating a DBMS table, it is sometimes better to execute a create table with DDL first, and then insert the data. Usually more efficient.
SAS/ACCESS products have a BULKLOAD=YES data set option to make inserting rows into database tables more efficient.
Is is possible to see how to create a LIBNAME using the SAS/ACCESS Interface to ODBC connection? I have had issues using the Pass-Through because users do not want to put in a password in their scripts.
In the webinar on ODBC connection to Oracle is demonstrated. The requires that you first create an ODBC data source name (DSN) to Oracle and then SAS connects via that DSN.
http://www.interfaceware.com/manual/odbc_oracle.html .
And then you can issue the ODBC LIBNAME statement. Here is the link to the SAS documentation: http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=acreldb&docsetTarget=n1...
PROC PWENCODE will allow you to encode a password so that it is not merely text that can be viewed or displayed with the SYMBOLGEN system option if the password is a macro variable.
Will these commands work on SAS Enterprise Guide as well as the server?
Yes, the code used in the webinar works in any SAS editor, including Enterprise Guide. And the various SAS editors, including Enterprise Guide, can submit code either to remote SAS servers or to SAS running on the local machine. You can configure either your local SAS or the remote SAS server to be able to connect to database data sources.
Can SAS/ACCESS access SAP Hana data server?
Yes, we have SAS/ACCESS to SAP Hana. Here is the link to the documentation:
Is there another webinar that explains more of the details on how to determine the initial connectin information for the databases and how to set up the database client software?
No webinar is available in the SAS Communities. For database client software configuration, I recommend consulting with the DBA for the database of interest since the database client software may well be familiar with this piece of the puzzle and how it needs to be configured in your organization:
The DBA can also supply the information about server names, ports, schemas, and other connection information.
SAS provides many other connection options to customize how and when users connect to the database. All the specifics for each database can be found in SAS documentation. For example, this is the link to SAS/ACCESS Interface to Oracle: http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=acreldb&docsetTarget=p1...
When PROC MEANS is submitted to data base, is only the highest level calculated, i.e., NWAY?
Yes, only the highest level summary (NWAY) is calculated in-database. Once that NWAY summary is returned to SAS, SAS is able to derive the lower level summaries from the NWAY result. You can see this by using the system option SASTRACE=',,,d' as discussed in the webinar. The generated code will reveal that only that NWAY summarization is calculated by the SQL code generated and submitted to the database.
How do you find out which SAS functions are translatable to databases, i.e., SCAN function is not.
There is a section in each database documentation with the SAS functions that are passed to the database. For example, here is the documentation for functions passed to Oracle: http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=acreldb&docsetTarget=p0....
This is the link to all databases: http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=acreldb&docsetTarget=n0...
NOTE: For best results when opening the attached slides and program code, click on the “download” icon.
