2. SAS Data Management Tools and Applications Flashcards
What is a SAS/ACCESS LIBNAME?
A SAS programmer uses a LIBNAME statement to connect to the DBMS. DBMS tables can be named in a SAS program wherever SAS data sets can be named. SAS implicitly converts the SAS code into native database SQL statements.
What is SQL pass-through?
A SAS programmer uses the SQL procedure in a SAS session to write and submit native database SQL to the database.
What are the 2 types of SQL pass-through components that you can submit to the DBMS?
SELECT - produce output to SAS software (SQL procedure pass-through queries)
EXECUTE - perform all other non-query SQL statements that do not produce output (for example, GRANT, CREATE, or DROP) i.e. DDL
What is the SASTRACE option?
SASTRACE is an options statement which when used, the SAS log will display the details about the native database SQL that is generated by the SAS/ACCESS engines. Developers can use SASTRACE to evaluate queries to ensure they are producing efficient code and not needlessly returning large amounts of data from the RDBMS
How are the SAS Platform Applications organized?
Analytics applications
Reporting applications
Programming applications
Data Management applications
What is DataFlux Data Management Studio?
Combines data profiling, data cleansing, entity resolution, and monitoring tools for incorporating data quality into an information management process.
SAS Data Integration Studio provides role-based security. True or False?
False
What is a SAS Package?
A SAS internal format that can be used to move metadata between SAS metadata repositories and maintain metadata backups. It supports most SAS platform metadata objects including objects relevant to SAS Data Integration Studio, such as jobs, libraries, tables, and external files.
How do you import a package in SAS Data Integration Studio?
Right-click a folder from the Folders tab
Select Import
Navigate to the SAS Package
Verify the objects to Import
Import
What does the status bar in SAS Data Integration Studio display?
The status bar displays the name of the currently selected object, the name of the default SAS Application Server if one is selected, the login ID and metadata identity of the current user, and the name of the current SAS Metadata Server.
What components are accessed through the Tree View in SAS Data Integration Studio?
Folders Tree - displays the metadata folder hierarchy that is shared across the SAS platform and can be customized
Inventory Tree - displays metadata in predefined categories and is not customizable
Transformations Tree - provides access to transformations that can be added to SAS Data Integration Studio jobs
Checkouts Tree - available to users working under change management
Basic Properties pane - displays the basic properties of an object selected in a tree view
What is the Job Editor Window in SAS Data Integration Studio?
The Job Editor window enables you to create, run, and troubleshoot SAS Data Integration Studio jobs
What are the tabs in the Job Editor Window in SAS Data Integration Studio?
- The Diagram tab is used to build and update the process flow for a job.
- The Code tab is used to review or update code for a job.
- The Log tab is used to review the log for a submitted job.
- The Output tab is used to review the output of a submitted job.
- The Details pane is used to monitor and debug a job in the Job Editor.
What is the Options Window in SAS Data Integration Studio?
The Options window is used to specify global settings for SAS Data Integration Studio options.
What is a library?
A library is a collection of one or more files that are referenced as a unit.
- provide access to source tables and target tables
- can be created in SAS Management Console by an administrator
- can be created in SAS Data Integration Studio by a Data Integration developer
- are created with the New Library Wizard.
What are 2 ways of defining SAS Metadata Objects for Source Data in SAS Data Integration Studio?
- The Register Tables Wizard can be used to register metadata for existing tables.
- The External File wizards can be used to register metadata for an external file.
What are 3 items that the Register Tables Wizard in SAS Data Integration Studio defines?
- type of table (DBMS type)
- library
- metadata location
What are 3 items that you define with the External File Wizard in SAS Data Integration Studio?
- file to read
- parameters
- column definition
What are the dependencies for SAS Table Metadata in SAS Data Integration Studio?
the library object is used by the table object
the table object is dependent on the library object.
What are the dependencies for DBMS Table Metadata in SAS Data Integration Studio?
the server object is used by the library object,
the library object is used by the table object.
the table object is dependent on the library object
the library object is dependent on the server object.
What are the dependencies for ODBC Table Metadata in SAS Data Integration Studio?
the ODBC data source is used by the server object
the server object is used by the library object
the library object is used by the table object.
the table object is dependent on the library object,
the library object is dependent on the server object
the server object is dependent on the ODBC data source.
What tasks can be performed to define metadata for a new table using the New Table Wizard in SAS Data Integration Studio?
- import metadata from tables/columns that are already registered in the metadata repository
- override metadata that was imported (for example, change a column name)
- define new attributes for the table that is defined (for example, indexes)
What is the Common Warehouse Metamodel (CWM)?
an industry-standard format that is supported by many software vendors. The CWM format supports relational metadata such as tables, columns, indexes, and keys.
Relational metadata, including the CWM format, can be used to exchange relational metadata between software applications and import models from third-party data modeling tools into SAS Data Integration Studio.
What are SAS Metadata Bridges?
A tool for reading into SAS each supported vendor-specific CWM format.
What are jobs (processes) in SAS Data Integration Studio?
Jobs read from sources, perform necessary data transformations, and load targets. In other words, a job is a metadata object that organizes sources, targets, and transformations into processes that create output
What are tan-colored objects in a SAS Data Integration Studio process flow?
table and external file objects
What are blue-colored objects in a SAS Data Integration Studio process flow?
transformations
What is a transformation in SAS Data Integration Studio?
A transformation allows you to specify how to extract data, transform data, or load data into data stores. Each transformation that you specify in a process flow diagram generates or retrieves SAS code which can be customized.
What is the Splitter transformation in SAS Data Integration Studio?
The Splitter transformation is found in the Data category of transformations and can be used to create one or more subsets of a source.
What is the Join Transformation in SAS Data Integration Studio?
The Join transformation is found in the SQL category of transformations and generates PROC SQL code.
It is used to create an SQL query that runs in the context of a SAS Data Integration Studio job and supports all the join types such as inner join, left join, right join, and full
join
It supports subqueries and pass-through SQL and features a graphical interface for building and configuring the components of the SQL query.
What are the main views in the Join Designer Window of SAS Data Integration Studio?
- Main display area with Diagram tab, Code tab, and Log tab
- Navigate pane - navigate the current query
- SQL Clauses pane - add SQL clauses or edit the join type
- Properties pane - view and edit properties of a selected item
What is the File Reader transformation in SAS Data Integration Studio?
A transformation used to read external files. This is not needed for SAS files.
What is the Extract transformation in SAS Data Integration Studio?
Generates PROC SQL code and supports the following items:
* one source table
* SELECT clause
* derived columns
* WHERE clause
* GROUP BY clause
* ORDER BY clause
What is the Summary Statistics transformation in SAS Data Integration Studio?
The Summary Statistics transformation generates PROC MEANS code and provides the following items:
* descriptive statistics like SUM, MEAN, and STD
* multiple analysis variables (for example, revenue and profit)
* multiple classification (group by) variables (for example, gender and age group)
* output to a report by default
* output to a table
What are the SQL transformations in SAS Data Integration Studio?
The transformations in the SQL group generate SQL code and enable you to
* create tables (SAS and DBMS)
* delete rows from a table
* execute SQL statements in a DBMS
* extract rows from a source table
* insert rows into a target table
* join tables
* SQL merge (update or insert)
* perform set operations
* update rows in a target table.
What is the Set Operators Transformation in SAS Data Integration Studio?
Set operations allow multiple query result sets to be combined into a single result set. The sets are combined vertically, with operations combing rows from the source sets in different ways depending on the operation.
UNIONS, EXCEPTS, INTESECTS
What are Custom Transformation in SAS Data Integration Studio?
Custom transformations give data integration developers the flexibility to extend the functional capabilities of SAS Data Integration Studio.
All transformations, including custom ones, generate SAS code to accomplish the following:
* extract data
* transform data
* load data into data stores
* create reports
What are the two transformation templates available in SAS Data Integration Studio?
Java Plug-in Transformation Templates - Created with the Java programming language
Generated Transformation Templates - Created with the New Transformation Wizard and SAS code
What are options and option groups in custom transformations in SAS Data Integration Studio?
Options and option groups are related to macro variables. When combined with macro variables, users can select values on the Options tab in the transformation properties to configure the transformation. These values will be assigned to the corresponding macro variables in the code.
Options are surfaced to users on the Options tab of the transformation when they use the transformation in a job. Groups become the organizational categories in the left-hand column of the Options tab, and prompts become options.
Basically parameters for custom transformations
What is the SAS Quality Knowledge Base (QKB)?
a collection of files and algorithms that store data and logic for defining data management operations such as data cleansing and standardization. The definitions in the QKB that perform the cleansing tasks are geography and language-specific.
What is a locale?
a combination of geography and language used in the QKB
Where are QKB components modified?
In SAS Data Management Studio (aka DataFlux)
What is a QKB definition?
A definition is a set of steps for processing data values. Definitions are organized by type.
What are the types of QKB definitions?
- Case - Transforms a text string by changing the case of its characters to uppercase, lowercase, or proper case.
- Extraction - Extracts parts of the text string and assigns them to corresponding tokens for the specified data type.
- Gender Analysis - Guesses the gender of the individual in the text string.
- Identification Analysis - Identifies the text string as referring to a particular predefined category.
- Language Guess - Guesses the language of a text string.
- Locale Guess - Guesses the locale of a text string.
- Match Generates - match codes for text strings where the match codes denote a fuzzy representation of the character content of the tokens in the text string.
- Parse - Parses a text string into meaningful tokens.
- Pattern Analysis - Transforms a text string into a particular pattern.
- Standardization - Transforms a text string into a standard format.