SAS expertise delivered to your desktop -- on-demand and free!

Join Now

SAS ACCESS: Universal SAS Methods to Access Just About Any Data, Anywhere

by SAS Employee DavidGhan on ‎07-26-2017 04:35 PM - edited 2 weeks ago by SAS Employee john_bauman (1,909 Views)

If you missed the Ask the Expert session on SAS ACCESS: Universal SAS Methods to Access Just About Any Data, Anywhere you can still view it on-demand at any time.


Watch the webinar

This Ask the Expert session will illustrate two universal SAS syntax methods for reading and writing database tables: SQL pass-through and the SAS ACCESS LIBNAME method. Examples of executing these methods will be 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 will be discussed.

demo code.png

Here is a summary of the Q&A segment held at the end of the session for ease of reference. Some extra links to further information have also been added.


What data types does SAS support with SAS/ACCESS?


SAS generally supports all the basic data types, and not the more complex ones. This is documented for each database in the SAS Access documentation and varies somewhat by database. But everything is converted by the ACCESS engine to the only two data types SAS supports (character and numeric). Database dates are converted to the numeric SAS date value and automatically assigned a default date format. Complex data types are not supported for the most part, but in some case may simply be converted to character values and treated as a literal character string.


If you copy an indexed SAS dataset to a database, is there a way to re-create the index in the database table?


It does not happend automatically as part of the copy process has been my general experience and as far as I know this is generally true. What you can do though, is add some SQL pass-through to submit database code to add an index to the tables after you create them.


How do I name columns from an excel spreadsheet in my SAS code. I've had trouble working with columns that have spaces in their name.


For the LIBNAME access method this depends depends on the system option called validvarname. If using value of V7, the access engine will put underscore characters in place of invalid characters such as spaces, so 'My Var' becomes My_Var. If validvarname is set to ANY then the oroginal name with spaces and other invalid characters are retained and instead you need to quote the varialbe name, as it is, and add the letter n after the quoted name (so 'My Var'n).


What is the quickest best way to do a lookup for data from a small SAS dataset. I need to find the matching rows in a large database table and return some values for those rows in the database.


There are several ways to approach this. One is to simply copy the small dataset into the database if you have write access to the database, and perform an inner join in the database. Then you can return the result back to SAS. There is also an option you can add on the SAS ACCESS LIBNAME statement (multi_data_src=inclause). If you then do an inner join of the sas dataset with a database table that option causes SAS to first find the unique values for the key value in the SAS dataset and then generate a query to ONLY bring back the matching rows from the database (see documentation here). Even better, if the database is indexed for the key value for the lookup, then you can use a LIBNAME or dataset option in the join (dbindex=yes) which instructs the Access engine to use the index to retrieve each needed row from the database. Click here for documentation on dbindex.



Want more tips? Be sure to subscribe to the Ask the Expert Community Library to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. From the Ask the Expert Library, just click Subscribe from the orange bar underneath the list of the recent articles.


NOTE: For best results when opening the attached slides and program code, click on the “download” icon.