Mastering SAS programming for data warehousing : an advanced programming guide to designing and managing data warehouses using SAS

Cover
Title Page
Copyright and Credits
Dedication
About Packt
Contributors
Table of Contents
Preface
Section 1: Managing Data in a SAS Data Warehouse
Chapter 1: Using SAS in a Data Mart, Data Lake, or Data Warehouse
	Technical requirements
	Using original versions of SAS
		Initial SAS data handling
		Early SAS data handling
		SAS data handling improvements
	Accessing data in SAS
		Upgrading to mainframes
		Transitioning to personal computers
		Reading external files
	Improving I/O
		Developing warehouse environments
		Using the WHERE clause
		Using IF compared to WHERE
		Sorting in SAS
		Setting indexes on variables
	Dealing with storage and memory issues
		Avoiding memory issues
		Accommodating Structured Query Language
		Using PROC SQL
		Using SAS today in a warehouse environment
		Using SAS in the cloud
	Using SAS in modern warehousing
		Warehousing unstructured text
		Using SAS components for warehousing
		Using other applications with SAS
		Connecting to Snowflake
	Summary
	Questions
	Further reading
Chapter 2: Reading Big Data into SAS
	Technical requirements
	Reading data extracts into SAS
		Understanding SAS datasets
		Working with the WORK directory
		Specifying LIBNAME
		Reading in SAS datasets
	Using the SAS XPT format
		Storing data in XPT format
		Creating an XPT file
		Comparing PROC CPORT/CIMPORT to PROC COPY
		Reading in XPTs using the XPORT engine
	Working with other file formats
		Reading non-SAS data formats
		Using PROC IMPORT
		Converting non-SAS data to SAS format
	Dealing with difficult data
		Understanding legacy data
		Reading data with difficult formats
		Specifying data locations in a fixed-width file
		Troubleshooting reading data after transport
	Summary
	Questions
	Further reading
Chapter 3: Helpful PROCs for Managing Data
	Technical requirements
	PROCs for understanding data
		Using PROC CONTENTS to understand data
		Documenting SAS data with codebooks
		Using labels for variables
		Adding user-defined formats to categorical variables
		Using native SAS formats with numeric variables
		Applying user-defined formats to continuous variables
	Using labels and formats in processing
		Using PROCs with labels and formats
		Maintaining labels and formats
		Alternatives to using labels and formats in a warehouse setting
	Viewing data in SAS
		Using PROC PRINT to view data
		Using PROC SQL to view data
		Using arithmetic operators in SAS
		Viewing data through SAS windows
	Summary
	Questions
	Further reading
Chapter 4: Managing ETL in SAS
	Technical requirements
	Setting up an analytic environment
		Designating storage and user groups
		Managing documentation storage
		Setting naming conventions for datasets
	Planning for data transformation
		Understanding arrays in SAS
		Setting naming conventions for variables
		Setting naming conventions and style for code
	Developing policy
		Setting format and label policies
		Setting data transfer policies
		Setting other policies
	Summary
	Questions
	Further reading
Chapter 5: Managing Data Reporting in SAS
	Technical requirements
	Using the ODS for data files
		Identifying available tables in the ODS
		Identifying internal tables in the log
		Outputting internal tables using the ODS
	Using the ODS for graphics files
		Outputting graphics from analytic PROCs
		Outputting graphics in different formats
		Setting system options
	SAS PROCs designed for reporting
		Using PROC REPORT
		Understanding the basics of PROC TABULATE
		Preparing data for PROC TABULATE
		Formulating PROC TABULATE code
		Using PROC SGPLOT
		Using PROC SGPANEL and PROC SGSCATTER
		Using PROC TEMPLATE with PROC SGRENDER
	Summary
	Questions
	Further reading
Section 2: Using SAS for Extract-Transform-Load (ETL) Protocols in a Data Warehouse
Chapter 6: Standardizing Coding Using SAS Arrays
	Technical requirements
	Understanding examples of arrays used to create variables
		Scenarios where arrays are useful
		Arrays as temporary objects
		Using arrays to create variables
	Conditions and index variables in array processing
		Adding a condition to array processing
		Creating index variables from array outputs
		Documenting and standardizing array processing
	Limitations of arrays
		Naming limitations in SAS arrays
		Naming limitations arrays impose on data storage
		Difficulty in troubleshooting
	Summary
	Questions
	Further reading
Chapter 7: Designing and Developing ETL Code in SAS
	Technical requirements
	Planning the ETL approach
		Specifying data with a data dictionary
		Understanding default PROC FREQ
		Using options to manipulate PROC FREQ output
		Using PROC UNIVARIATE for troubleshooting
		Using PROC FREQ to troubleshoot continuous variables
		Making plots for troubleshooting
		Choosing variables to serve to users
		Creating and maintaining formats for variables
	Creating transformation code
		Designing categorical grouping variables
		Cleaning up continuous variables
		Designing indicator variables
		Considering dates and numerical variables
		Exporting the transformed dataset
	Summary
	Questions
	Further reading
Chapter 8: Using Macros to Automate ETL in SAS
	Technical requirements
	Creating macros out of data step code
		Choosing to use macros and macro variables
		Using macro variables with the %LET command
		Using the log file with macro variables and macros
		Making macros with PROCs
		Making macros with data steps
		Addition conditions to macros
	Storing and calling macros
		Storing and calling macros in the same code
		Storing macros separately and calling them from code
	Loading transformed data
	Summary
	Questions
	Further reading
Chapter 9: Debugging and Troubleshooting in SAS
	Technical requirements
	Debugging data step code
		Writing well-formed and well-formatted code
		Using log information as guidance
		Troubleshooting strategies for data steps
	Debugging the do loop code
		Using the original data step debugger
		Using the data step debugger in SAS Enterprise Guide
	Debugging SAS macros
		Avoiding errors through the design process
		Using %PUT to display values of macro variables
		Setting system options to help with debugging macros
	Summary
	Questions
	Further reading
Section 3: Using SAS When Serving Warehouse Data to Users
Chapter 10: Considering User Needs of SAS Data Warehouses
	Technical requirements
	Needs of data warehouse users
		Considering classes of data warehouse users
		Considering the needs of each class of users
	Data stewardship for serving warehouse users
		Providing data access
		Serving needs created through the warehouse structure
		Adding, using, and serving up foreign keys
		Crosswalking data over time
	Data stewardship for serving warehouse developers
		Managing a data stewardship committee
		Providing curation and other support
	Summary
	Questions
	Further reading
Chapter 11: Connecting the SAS Data Warehouse to Other Systems
	Technical requirements
	Serving SAS to other systems
		Implementing de-identification policies
		Serving up a star schema
	Connecting to non-SAS data storage
		Understanding SQL views
		Using SAS to copy data from a remote data system
		Leveraging PROC SQL views for data transfer
		Exporting SAS data to non-SAS data storage
	Innovations in integrating SAS in reporting functions
	Summary
	Questions
	Further reading
Chapter 12: Using the ODS for Visualization in SAS
	Technical requirements
	The basics of using the ODS for data visualization
		Using macros in reporting
		Connecting to data in Snowflake
	Serving SAS data to the web with the ODS
		Interacting with SAS data over the web
		Using the SAS Enterprise Guide
		Using SAS Viya
	Using SAS and R for visualizations
		Reporting SAS data in Tableau
	Considerations when reporting SAS warehouse data
	Summary
	Questions
	Further reading
Assessments
Other Books You May Enjoy
Index