We’re smarter together. Learn from this collection of community knowledge and add your expertise.

SAS ACCESS: Universal SAS Methods to Access Just About Any Data, Anywhere - Ask the Expert Q&A

by SAS Employee DavidGhan 4 weeks ago - edited 4 weeks ago by Community Manager (1,241 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.

 

     demo code.png

 

I've also attached the slides and the program code that was demonstrated.

This session illustrates 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 are 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 are also discussed.

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.

 

Q. What data types does SAS support with SAS/ACCESS?

A: 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.

 

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

A: 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.

 

Q. 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.

A: 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).

 

Q. 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.

A: 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.

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.