SAS P1 L7 Flashcards
How assign a libref to an Excel workbook?
How will worksheets be handled?
- Use SAS/Access LIBNAME statement to assign libref to Excel workbook.
- SAS will treat each worksheet in workbook as though it were an SAS data set.
What does SAS/Access do?
What does it use?
- SAS/Access provides data connectivity and integration between SAS and third party data sources.
- Examples: Microsoft Excel, Oracle, other db - Uses special interfaces (bought separately) to read data from and write data to other databases, same as reading, writing to SAS library
- called SAS/Access Data Access Engines
- must be included in SAS installation
How find out what is included in SAS license on site?
Syntax:
Use PROC SETINIT to find out what is included in local license.
PROC SETINIT;
RUN;
Will generate list of all licensed SAS products and other info such as site number for tech support.
Syntax for using SAS/Access LIBNAME statement to access database?
How cite workbook?
LIBNAME libref {engine} “workbook-name” {options};
workbook-name needs to include path, file name, extension.
What is bitness?
Why does it matter?
Bitness is how many bits data uses to encode - SAS, Excel can use either 32 or 64 bits. Need to use correct Access engine to account for data matching.
Working between SAS and Excel files:
If bitness is the same, which Access engine can be used?
If bitness is different, which engine must be used?
What if remote server?
Bitness is same: use default (Excel Access) engine.
Bitness is different: use pcfiles server engine.
Can’t use pcfiles with remote server. ? what to use.
Syntax of LIBNAME statement with default engine?
Syntax of LIBNAME statement with pcfiles engine?
LIBNAME libref {engine} “workbook-name” {options};
LIBNAME libref pcfiles {PATH=}”workbook-name” {optionss};
- must have PATH= for pcfile
What is appearance of library icons if using data outside SAS?
library icons look different – have little globe in front – indicate data is outside SAS
How are excel files organized after opened with engine?
What if it is a spreadsheet ?
- worksheet = data set within “library”
- if worksheet name ends with $, = spreadsheet
Using excel spreadsheet: name ends in $
Problem: valid SAS data set name CANNOT contain $
How handle this?
Will need to refer to worksheet in special way.
Get around by using SAS name literal.
What is an SAS name literal?
How do you write it?
What does it allow you to do?
Example?
- SAS name literal: name token that is expressed as a string within quotation marks, followed by upper or lower case N.
- This enables special characters or blanks in data set names.
- Ex: libref.’worksheetname$’n
What happens when SAS has libref assigned to Excel workbook?
if want to use workbook in Excel
Workbook CANNOT be opened in Excel when SAS has libref assigned to it.
Must dissociate file first.
How do you dissociate a libref from an Excel file?
Example: with orionx
Dissociate at end via:
LIBNAM libref CLEAR;
Ex: LIBNAM orionx CLEAR;
(orionx = libref name)
Disconnects from data source, closes resources associated with connection.How access Oracle database?
Which engine to use?
To access Oracle db, use LIBNAME statement:
LIBNAME libref engine {SAS/Access options};
SAS/Access engine = ORACLE
What are other connection options required to access Oracle db?
user= optional oracle user name password= (or pw=) must be used if have "user=" path= Oracle driver, node and database. SAS/Access uses same Oracle path designation that you use to connect to Oracle directly. schema= schema allows you to read database objects, such as tables and views. If eliminated will get default schema.
