book

SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition

Name: SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition
Author: SAS Institute
ISBN: 9781607640448

by SAS Institute

June 2009

Intermediate to advanced

1000 pages

21h 34m

English

SAS Institute

Read now

Unlock full access

Copyright
About This Book
PurposeAudiencePrerequisitesHow to Create Practice DataSyntax ConventionsSAS Certification Practice Exam: Advanced Programming for SAS®9SAS Advanced Programming for SAS®9 ExamAdditional Resources
1. SQL Processing With SAS
1. Performing Queries Using PROC SQL
OverviewIntroductionObjectivesPROC SQL BasicsHow PROC SQL Is UniqueWriting a PROC SQL StepThe SELECT StatementSelecting ColumnsCreating New ColumnsSpecifying the TableSpecifying Subsetting CriteriaOrdering RowsOrdering by Multiple ColumnsQuerying Multiple TablesSpecifying Columns That Appear in Multiple TablesSpecifying Multiple Table NamesSubsetting RowsOrdering RowsSummarizing Groups of DataExampleSummary FunctionsCreating Output TablesExampleAdditional FeaturesSummaryText SummaryPROC SQL BasicsWriting a PROC SQL StepSelecting ColumnsSpecifying the TableSpecifying Subsetting CriteriaOrdering RowsQuerying Multiple TablesSummarizing Groups of DataCreating Output TablesAdditional FeaturesSyntaxSample ProgramsQuerying a TableSummarizing Groups of DataCreating a Table from the Results of a Query on Two TablesPoints to RememberQuiz
2. Performing Advanced Queries Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesViewing SELECT Statement SyntaxDisplaying All ColumnsUsing SELECT *Using the FEEDBACK OptionLimiting the Number of Rows DisplayedExampleEliminating Duplicate Rows from OutputExampleSubsetting Rows by Using Conditional OperatorsUsing Operators in PROC SQLUsing the BETWEEN-AND Operator to Select within a Range of ValuesUsing the CONTAINS or Question Mark (?) Operator to Select a StringExampleUsing the IN Operator to Select Values from a ListUsing the IS MISSING or IS NULL Operator to Select Missing ValuesExampleUsing the LIKE Operator to Select a PatternSpecifying a PatternExampleUsing the Sounds-Like (=*) Operator to Select a Spelling VariationSubsetting Rows by Using Calculated ValuesUnderstanding How PROC SQL Processes Calculated ColumnsUsing the Keyword CALCULATEDEnhancing Query OutputSpecifying Column Formats and LabelsSpecifying Titles and FootnotesAdding a Character Constant to OutputSummarizing and Grouping DataNumber of Arguments and Summary Function ProcessingGroups and Summary Function ProcessingSELECT Clause Columns and Summary Function ProcessingUsing a Summary Function with a Single Argument (Column)Using a Summary Function with Multiple Arguments (Columns)Using a Summary Function without a GROUP BY ClauseUsing a Summary Function with Columns Outside of the FunctionUsing a Summary Function with a GROUP BY ClauseCounting Values by Using the COUNT Summary FunctionCounting All RowsCounting All Non-Missing Values in a ColumnCounting All Unique Values in a ColumnSelecting Groups by Using the HAVING ClauseUnderstanding Data RemergingExampleSubsetting Data by Using SubqueriesIntroducing SubqueriesTypes of SubqueriesSubsetting Data by Using Noncorrelated SubqueriesUsing Single-Value Noncorrelated SubqueriesUsing Multiple-Value Noncorrelated SubqueriesExampleUsing Comparisons with SubqueriesUsing the ANY OperatorExampleUsing the ALL OperatorExampleSubsetting Data by Using Correlated SubqueriesExampleUsing the EXISTS and NOT EXISTS Conditional OperatorsExample: Correlated Subquery with NOT EXISTSValidating Query SyntaxUsing the NOEXEC OptionUsing the VALIDATE KeywordAdditional FeaturesSummaryText SummaryViewing SELECT Statement SyntaxDisplaying All ColumnsLimiting the Number of Rows DisplayedEliminating Duplicate Rows from OutputSubsetting Rows by Using Conditional OperatorsSubsetting Rows by Using Calculated ValuesEnhancing Query OutputSummarizing and Grouping DataSubsetting Data by Using SubqueriesSubsetting Data by Using Noncorrelated SubqueriesSubsetting Data by Using Correlated SubqueriesValidating Query SyntaxAdditional FeaturesSyntaxSample ProgramsDisplaying all Columns in Output and an Expanded Column List in the SAS LogEliminating Duplicate Rows from OutputSubsetting Rows by Using Calculated ValuesSubsetting Data by Using a Noncorrelated SubquerySubsetting Data by Using a Correlated SubqueryPoints to RememberQuiz
3. Combining Tables Horizontally Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesUnderstanding JoinsGenerating a Cartesian ProductUsing Inner JoinsIntroducing Inner Join SyntaxExampleUnderstanding How Joins Are ProcessedEliminating Duplicate ColumnsRenaming a Column by Using a Column AliasJoining Tables That Have Rows with Matching ValuesSpecifying a Table AliasExample: Complex PROC SQL Inner JoinExample: PROC SQL Inner Join with Summary FunctionsUsing Outer JoinsIntroducing Types of Outer JoinsUsing a Left Outer JoinUsing a Right Outer JoinUsing a Full Outer JoinExample: Outer JoinCreating an Inner Join with Outer Join-Style SyntaxComparing SQL Joins and DATA Step Match-MergesWhen All of the Values MatchWhen Only Some of the Values MatchWhen Only Some of the Values Match: Using the COALESCE FunctionUnderstanding the Advantages of PROC SQL JoinsUsing In-Line ViewsIntroducing In-Line ViewsReferencing an In-Line View with Other Views or TablesReferencing Multiple Tables in an In-Line ViewAssigning an Alias to an In-Line ViewExample: Query That Contains an In-Line ViewJoining Multiple Tables and ViewsExample: Complex Query That Combines Four TablesExample: Technique 1 (PROC SQL Subqueries, Joins, and In-Line Views)Query 1: Identify the crew for the Copenhagen (CPH) flightQuery 2: Find the states and job categories of the crew membersQuery 3: Find the employee numbers of the crew supervisorsQuery 4: Find the names of the supervisorsExample: Technique 2 (PROC SQL Multi-way Join with Reflexive Join)Example: Technique 3 (Traditional SAS Programming)SummaryText SummaryUnderstanding JoinsGenerating a Cartesian ProductUsing Inner JoinsUsing Outer JoinsCreating an Inner Join with Outer Join-Style SyntaxComparing SQL Joins and DATA Step Match-MergesUsing In-Line ViewsJoining Multiple Tables and ViewsSyntaxSample ProgramsCombining Tables by Using an Inner JoinCombining Tables by Using a Left Outer JoinOverlaying Common Columns in a Full Outer JoinJoining Tables by Using a Subquery and an In-Line ViewPoints to RememberQuiz
4. Combining Tables Vertically Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesUnderstanding Set OperationsExampleProcessing a Single Set OperationUsing Multiple Set OperatorsExampleProcessing Multiple Set OperationsIntroducing Set OperatorsProcessing Unique versus Duplicate RowsCombining and Overlaying ColumnsModifying Results by Using KeywordsUsing the EXCEPT Set OperatorUsing the EXCEPT Operator AloneUsing the Keyword ALL with the EXCEPT OperatorUsing the Keyword CORR with the EXCEPT OperatorUsing the Keywords ALL and CORR with the EXCEPT OperatorExample: EXCEPT OperatorExample: EXCEPT Operator in an In-Line ViewUsing the INTERSECT Set OperatorUsing the INTERSECT Operator AloneUsing the Keyword ALL with the INTERSECT OperatorUsing the Keyword CORR with the INTERSECT OperatorUsing the Keywords ALL and CORR with the INTERSECT OperatorExample: INTERSECT OperatorUsing the UNION Set OperatorUsing the UNION Operator AloneUsing the Keyword ALL with the UNION OperatorUsing the Keyword CORR with the UNION OperatorUsing the Keywords ALL and CORR with the UNION OperatorExample: UNION OperatorExample: UNION Operator and Summary FunctionsUsing the OUTER UNION Set OperatorUsing the OUTER UNION Operator AloneUsing the Keyword CORR with the OUTER UNION OperatorExample: OUTER UNION OperatorComparing Outer Unions and Other SAS TechniquesProgram 1: PROC SQL OUTER UNION Set Operation with CORRProgram 2: DATA Step, SET Statement, and PROC PRINT StepSummaryText SummaryUnderstanding Set OperationsUsing the EXCEPT Set OperatorUsing the INTERSECT Set OperatorUsing the UNION Set OperatorUsing the OUTER UNION Set OperatorComparing Outer Unions and Other SAS TechniquesSyntaxSample ProgramPoints to RememberQuiz
5. Creating and Managing Tables Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesUnderstanding Methods of Creating TablesCreating an Empty Table by Defining ColumnsExampleSpecifying Data TypesSpecifying Column WidthsSpecifying Column ModifiersExampleDisplaying the Structure of a TableExampleCreating an Empty Table That Is Like Another TableExampleSpecifying a Subset of Columns from a TableExampleCreating a Table from a Query ResultExampleCopying a TableExampleInserting Rows of Data into a TableInserting Rows by Using the SET ClauseExampleInserting Rows by Using the VALUES ClauseExampleInserting Rows from a Query ResultExampleCreating a Table That Has Integrity ConstraintsGeneral Integrity ConstraintsReferential Integrity ConstraintsCreating a Constraint in a Column SpecificationExampleCreating a Constraint by Using a Constraint SpecificationExampleHandling Errors in Row InsertionsExampleUsing the UNDO_POLICY= Option to Control UNDO ProcessingExampleDisplaying Integrity Constraints for a TableExampleUpdating Values in Existing Table RowsUpdating Rows by Using the Same ExpressionExampleUpdating Rows by Using Different ExpressionsExampleHow PROC SQL Updates Rows Based on a CASE ExpressionHow the Case Operand WorksUpdating Rows by Using the CASE Expression without a Case OperandExampleUpdating Rows by Using the CASE Expression with a Case OperandExampleUsing the CASE Expression in the SELECT StatementExampleDeleting Rows in a TableExampleAltering Columns in a TableAdding Columns to a TableExampleDropping Columns from a TableExampleModifying Columns in a TableExampleAdding, Dropping, and Modifying Columns in a Single StatementExampleDropping TablesExampleSummaryText SummaryUnderstanding Methods of Creating TablesCreating an Empty Table by Defining ColumnsDisplaying the Structure of a TableCreating an Empty Table That Is Like Another TableCreating a Table from a Query ResultInserting Rows of Data into a TableCreating a Table That Has Integrity ConstraintsHandling Errors in Row InsertionsDisplaying Integrity Constraints for a TableUpdating Values in Existing Table RowsDeleting Rows in a TableAltering Columns in a TableDropping TablesSyntaxSample ProgramsCreating an Empty Table by Defining ColumnsCreating an Empty Table That Is Like Another TableCreating a Table from a Query ResultDisplaying the Structure of a TableInserting Rows into a Table by Specifying Column Names and ValuesInserting Rows into a Table by Specifying Lists of ValuesInserting Rows into a Table from a Query ResultCreating a Table That Has Integrity ConstraintsDisplaying Integrity Constraints for a TableUpdating Rows in a Table Based on an ExpressionUpdating Rows in a Table by Using a CASE ExpressionUpdating Rows in a Table by Using a CASE Expression (Alternate Syntax)Deleting Rows in a TableAdding, Modifying, and Dropping Columns in a TableDropping a TablePoints to RememberQuiz
6. Creating and Managing Indexes Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesUnderstanding IndexesAccessing Rows in a TableSimple and Composite IndexesUnique IndexesExampleDeciding Whether to Create an IndexPROC SQL Queries That Can Be Optimized by an IndexBenefits of Using an IndexExample: Using an Index to Access a Small Subset of DataUnderstanding the Costs of Using an IndexGuidelines for Creating IndexesCreating an IndexCreating Multiple IndexesExample: Creating a Simple IndexExample: Creating a Composite, Unique IndexDisplaying Index SpecificationsAlternatives to the DESCRIBE TABLE StatementExampleManaging Index UsageUnderstanding How SAS Decides Whether to Use an IndexDetermining Whether SAS Is Using an IndexExample: Query That Uses an IndexExample: Query That Does Not Use an IndexControlling Index UsageUsing IDXWHERE= to Direct SAS to Use or Not to Use an IndexExampleUsing IDXNAME= to Direct SAS to Use a Specified IndexExampleDropping IndexesExample: Dropping a Composite IndexSummaryText SummaryUnderstanding IndexesDeciding Whether to Create an IndexCreating an IndexDisplaying Index SpecificationsManaging Index UsageDropping IndexesSyntaxSample ProgramsCreating a Simple, Unique Index and a Composite IndexDisplaying Index SpecificationsDetermining Whether SAS Is Using an IndexDirecting SAS to Ignore All IndexesDirecting SAS to Use a Specified IndexDropping an IndexPoints to RememberQuiz
7. Creating and Managing Views Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesCreating and Using PROC SQL ViewsPROC SQL ViewsCreating PROC SQL ViewsExampleUsing PROC SQL ViewsDisplaying the Definition for a PROC SQL ViewExampleManaging PROC SQL ViewsGuidelines for Using PROC SQL ViewsOmitting the LibrefUsing an Embedded LIBNAME StatementExampleCreating a View to Enhance Table SecurityExampleUpdating PROC SQL ViewsExampleDropping PROC SQL ViewsExampleSummaryText SummaryUsing PROC SQL ViewsCreating SQL ViewsDisplaying the Definition for a PROC SQL ViewManaging PROC SQL ViewsUpdating PROC SQL ViewsDropping PROC SQL ViewsSyntaxSample ProgramsCreating a PROC SQL ViewDisplaying the Definition for a PROC SQL ViewUsing a PROC SQL View in a QueryUpdating a PROC SQL ViewDropping a PROC SQL ViewPoints to RememberQuiz

8. Managing Processing Using PROC SQL
OverviewIntroductionObjectivesPrerequisitesSpecifying SQL OptionsControlling ExecutionRestricting Row ProcessingExampleControlling OutputIncluding a Column of Row NumbersExampleDouble-Spacing OutputExampleFlowing Characters within a ColumnExampleTesting and Evaluating PerformanceWriting Timing Information for Each StatementExampleResetting OptionsExampleUsing Dictionary TablesExploring and Using Dictionary TablesExampleExampleAdditional FeaturesRestricting the Number of LoopsStopping Execution in PROC SQL after an ErrorSummaryText SummarySpecifying SQL OptionsRestricting Row ProcessingControlling OutputTesting and Evaluating PerformanceResetting OptionsUsing Dictionary TablesAdditional FeaturesSyntaxSample ProgramsQuerying a Table Using PROC SQL OptionsDescribing and Querying a Dictionary TablePoints to RememberQuiz
2. SAS Macro Language
9. Introducing Macro Variables
OverviewIntroductionObjectivesBasic ConceptsMacro VariablesReferencing Macro VariablesExample: Referencing a Macro VariableExample: Referencing a Macro Variable in a TitleUsing Automatic Macro VariablesExampleUsing User-Defined Macro VariablesThe %LET StatementExample%LET Statement ExamplesProcessing Macro VariablesSAS ProcessingTokenizationExamplesMacro TriggersDisplaying Macro Variable Values in the SAS LogThe SYMBOLGEN OptionExampleThe %PUT StatementExampleUsing Macro Functions to Mask Special CharactersMacro Quoting FunctionsExampleThe %STR FunctionExampleThe %NRSTR FunctionExampleThe %BQUOTE FunctionExampleUsing Macro Functions to Manipulate Character StringsMacro Character FunctionsThe %UPCASE FunctionExampleThe %QUPCASE FunctionExampleThe %SUBSTR FunctionExampleThe %QSUBSTR FunctionExampleThe %INDEX FunctionExampleThe %SCAN FunctionExampleThe %QSCAN FunctionExampleUsing SAS Functions with Macro VariablesThe %SYSFUNC FunctionExampleQuoting with %QSYSFUNCExampleCombining Macro Variable References with TextDelimiters in Macro Variable NamesSummaryText SummaryBasic ConceptsUsing Automatic Macro VariablesUsing User-Defined Macro VariablesProcessing Macro VariablesDisplaying Macro Variable Values in the SAS LogUsing Macro Functions to Mask Special CharactersUsing Macro Functions to Manipulate Character StringsUsing SAS Functions with Macro VariablesCombining Macro Variable References with TextSyntaxSample ProgramsCreating Macro Variables with a %LET StatementUsing Automatic Macro VariablesInserting Macro Variables Immediately After TextInserting Macro Variables Immediately Before TextPoints to RememberQuiz
10. Processing Macro Variables at Execution Time
OverviewIntroductionObjectivesPrerequisitesCreating a Macro Variable During DATA Step ExecutionExampleThe SYMPUT RoutineUsing SYMPUT with a LiteralExampleUsing SYMPUT with a DATA Step VariableExampleThe SYMPUTX RoutineExampleUsing CALL SYMPUT with DATA Step ExpressionsExamplePUT FunctionExampleCreating Multiple Macro Variables During DATA Step ExecutionCreating Multiple Macro Variables with CALL SYMPUTExampleReferencing Macro Variables IndirectlyIntroductionThe Forward Re-Scan RuleExampleExampleExampleObtaining Macro Variable Values During DATA Step ExecutionThe SYMGET FunctionExampleCreating Macro Variables During PROC SQL Step ExecutionThe INTO Clause and the NOPRINT OptionExampleCreating Variables with the INTO ClauseExampleExampleCreating a Delimited List of ValuesExampleWorking with PROC SQL ViewsUsing Macro Variables in SCL ProgramsThe SYMPUTN RoutineExampleThe SYMGETN FunctionExampleSummaryText SummaryCreating a Macro Variable During DATA Step ExecutionCreating Multiple Macro Variables During DATA Step ExecutionReferencing Macro Variables IndirectlyObtaining Macro Variable Values During DATA Step ExecutionCreating Macro Variables During PROC SQL Step ExecutionWorking with PROC SQL ViewsUsing Macro Variables in SCL ProgramsSyntaxSample ProgramsUsing CALL SYMPUT to Create Macro VariablesReferencing Macro Variables IndirectlyUsing SYMGET to Obtain Macro Variable ValuesCreating Macro Variables with the INTO ClausePoints to RememberQuiz
11. Creating and Using Macro Programs
OverviewIntroductionObjectivesPrerequisitesBasic ConceptsDefining a MacroExampleCompiling a MacroThe MCOMPILENOTE OptionExampleCalling a MacroExampleMacro ExecutionExampleDeveloping and Debugging MacrosMonitoring Execution with System OptionsThe MPRINT OptionExampleThe MLOGIC OptionExampleComments in Macro ProgramsExampleUsing Macro ParametersExampleMacros That Include Positional ParametersExampleMacros That Include Keyword ParametersExampleMacros That Include Mixed Parameter ListsExampleMacros That Include the PARMBUFF OptionExampleUnderstanding Symbol TablesThe Global Symbol TableThe %GLOBAL StatementExampleThe Local Symbol TableThe %LOCAL StatementExampleRules for Creating and Updating VariablesMultiple Local Symbol TablesExampleThe MPRINTNEST OptionExampleThe MLOGICNEST OptionExampleProcessing Statements ConditionallyConditional Execution%IF-%THEN Compared to IF-THENExampleExampleMacro Execution with Conditional ProcessingExampleExampleConditional Processing of Parts of StatementsExampleCase Sensitivity in Macro ComparisonsExampleProcessing Statements IterativelyExampleExampleGenerating Complete StepsExampleUsing Arithmetic and Logical ExpressionsThe %EVAL FunctionExamplesExampleAutomatic EvaluationSummaryText SummaryBasic ConceptsDeveloping and Debugging MacrosUsing Macro ParametersUnderstanding Symbol TablesProcessing Statements ConditionallyProcessing Statements IterativelyUsing Arithmetic and Logical ExpressionsSyntaxSample ProgramsDefining a Basic MacroDefining a Macro with Positional ParametersDefining a Macro with Keyword ParametersDefining a Macro with Mixed ParametersUsing the %IF-%THEN StatementUsing the Iterative %DO StatementPoints to RememberQuiz
12. Storing Macro Programs
OverviewIntroductionObjectivesPrerequisitesUnderstanding Session-Compiled MacrosStoring Macro Definitions in External FilesExampleStoring Macro Definitions in Catalog SOURCE EntriesExampleThe CATALOG ProcedureExampleThe CATALOG Access MethodExampleExampleUsing the Autocall FacilityCreating an Autocall LibraryExampleDefault Autocall LibraryExampleAccessing Autocall MacrosExampleUsing Stored Compiled MacrosThe Stored Compiled Macro FacilityCreating a Stored Compiled MacroUsing the SOURCE OptionExampleAccessing Stored Compiled MacrosExampleAccessing Stored Macro CodeExampleSummaryText SummaryUnderstanding Session-Compiled MacrosStoring Macro Definitions in External FilesStoring Macro Definitions in Catalog SOURCE EntriesUsing the Autocall FacilityUsing Stored Compiled MacrosSyntaxSample ProgramsCompiling an Externally Stored Macro Definition with the %INCLUDE StatementListing the Contents of a CatalogUsing the Catalog Access MethodAccessing an Autocall MacroCreating a Stored Compiled MacroPoints to RememberQuiz
3. Advanced SAS Programming Techniques
13. Creating Samples and Indexes
OverviewIntroductionObjectivesPrerequisitesCreating a Systematic Sample from a Known Number of ObservationsExampleExampleCreating a Systematic Sample from an Unknown Number of ObservationsExampleCreating a Random Sample with ReplacementUsing the RANUNI FunctionExampleUsing a Multiplier with the RANUNI FunctionUsing the CEIL FunctionExampleExampleCreating a Random Sample without ReplacementExampleUsing IndexesTypes of IndexesCreating Indexes in the DATA StepExamplesExampleDetermining Whether SAS Is Using an IndexExampleManaging Indexes with PROC DATASETSExampleManaging Indexes with PROC SQLExampleDocumenting and Maintaining IndexesExampleExampleCopying Data SetsExamplesRenaming Data SetsExampleRenaming VariablesExampleSummaryText SummaryCreating a Systematic Sample from a Known Number of ObservationsCreating a Systematic Sample from an Unknown Number of ObservationsCreating a Random Sample with ReplacementCreating a Random Sample without ReplacementUsing IndexesCreating Indexes in the DATA StepManaging Indexes with PROC DATASETS and PROC SQLDocumenting and Maintaining IndexesSyntaxSample ProgramsCreating a Systematic Sample from a Known Number of ObservationsCreating a Systematic Sample from an Unknown Number of ObservationsCreating a Random Sample with ReplacementCreating a Random Sample without ReplacementCreating an Index in the DATA StepManaging Indexes with PROC DATASETSManaging Indexes with PROC SQLPoints to RememberQuiz
14. Combining Data Vertically
OverviewIntroductionObjectivesUsing a FILENAME StatementExampleUsing an INFILE StatementExampleAssigning the Names of the Files to Be ReadExampleUsing the COMPRESS FunctionExampleUsing the END= OptionExampleUsing Date FunctionsExampleUsing the INTNX FunctionExampleAppending SAS Data SetsExampleUsing the FORCE OptionExampleAppending Variables with Different LengthsExampleAppending Variables with Different TypesExampleAdditional FeaturesStoring Raw Data Filenames in a SAS Data SetStoring Raw Data Filenames in an External FileSummaryText SummaryUsing a FILENAME StatementUsing an INFILE StatementAppending SAS Data SetsAdditional FeaturesSyntaxCombining Raw Data Files Using a FILENAME StatementCombining Raw Data Files Using an INFILE StatementCombining SAS Data Sets Using PROC APPENDSample ProgramsCombining Raw Data Files Using a FILENAME StatementCombining Raw Data Files Using an INFILE StatementCombining SAS Data Sets Using PROC APPENDPoints to RememberQuiz
15. Combining Data Horizontally
OverviewIntroductionObjectivesPrerequisitesReviewing TerminologyRelationships between Input Data SourcesWorking with Lookup Values Outside of SAS Data SetsThe IF-THEN/ELSE StatementExample: Using the IF-THEN/ELSE Statement to Combine DataSAS ArraysExample: Using the ARRAY Statement to Combine DataUser-Defined SAS FormatsExample: Using the FORMAT Procedure to Combine DataCombining Data with the DATA Step Match-MergeThe DATA Step Match-MergeWorking with Multiple Lookup TablesExampleUsing PROC SQL to Join DataThe SQL ProcedureExample: Working with Multiple Lookup TablesComparing DATA Step Match-Merges and PROC SQL JoinsExamplesDATA Step Match-MergeExecution of a DATA Step Match-MergePROC SQL JoinExecution of a PROC SQL JoinExample: Combining Data from a Many-to-Many MatchUsing Multiple SET StatementsExample: Using Multiple SET Statements with a Many-to-Many MatchCombining Summary Data and Detail DataThe MEANS ProcedureExampleExampleThe Sum StatementExampleUsing an Index to Combine DataThe KEY= OptionExampleExampleExampleThe _IORC_ VariableExampleUsing a Transactional Data SetUsing the UPDATE StatementExampleSummaryText SummaryReviewing TerminologyWorking with Lookup Values Outside of SAS Data SetsCombining Data with the DATA Step Match-MergeUsing PROC SQL to Join DataComparing DATA Step Match-Merges and PROC SQL JoinsCombining Summary Data and Detail DataUsing an Index to Combine DataUsing a Transactional Data SetSyntaxSample ProgramsCombining Data with the IF-THEN/ELSE StatementCombining Data with the ARRAY StatementCombining Data with the FORMAT ProcedurePerforming a DATA Step Match-MergePerforming a PROC SQL JoinWorking with a Many-to-Many MatchCombining Summary Data and Detail DataUsing an Index to Combine DataUsing a Transactional Data SetPoints to RememberQuiz
16. Using Lookup Tables to Match Data
OverviewIntroductionObjectivesUsing Multidimensional ArraysReview of the Multidimensional Array StatementExampleUsing Stored Array ValuesExampleCreating an ArrayLoading the Array ElementsReading the Actual ValuesUsing PROC TRANSPOSEExampleAdding Descriptive Variable NamesMerging the Transposed Data SetStructuring the Data for a MergeUsing a BY Statement with PROC TRANSPOSESorting the Work.Ctarget2 Data SetReorganizing the Sasuser.Monthsum Data SetSorting the Work.Mnthsum2 Data SetCompleting the MergeUsing Hash Objects as Lookup TablesThe Structure of a Hash ObjectExampleData Step Component ObjectsDeclaring the Hash ObjectInstantiating the Hash ObjectDeclaring and Instantiating the Hash Object in a Single StepDeﬁning Keys and DataUsing the Call Missing RoutineLoading Key and Data ValuesRetrieving Matching DataHash Object ProcessingCreating a Hash Object From a SAS Data SetUsing a Non-Executing SET StatementWorking with Multiple Data VariablesRetrieving Multiple Data ValuesUsing Return Codes with the FIND MethodExampleSummaryText SummaryIntroductionUsing Multidimensional ArraysUsing Stored Array ValuesUsing PROC TRANSPOSEMerging the Transposed Data SetUsing Hash Objects as Lookup TablesSyntaxUsing a Multidimensional ArrayUsing Stored Array ValuesUsing PROC TRANSPOSE and a MergeUsing a Hash Object as a Lookup TableSample ProgramsUsing a Multidimensional ArrayUsing Stored Array ValuesUsing PROC TRANSPOSE and a MergeUsing a Hash Object That Is Loaded From Hard-Coded ValuesUsing a Hash Object That Is Loaded From a SAS Data SetPoints to RememberQuiz
17. Formatting Data
OverviewIntroductionObjectivesCreating Custom Formats Using the VALUE StatementReview of Creating Non-Overlapping FormatsCreating a Format with Overlapping RangesExampleCreating Custom Formats Using the PICTURE StatementWays to Specify PicturesExampleGuidelines for Specifying DirectivesExampleManaging Custom FormatsUsing FMTLIB with PROC FORMAT to Document FormatsExampleUsing PROC CATALOG to Manage FormatsExampleUsing Custom FormatsExampleUsing a Permanent Storage Location for FormatsExampleSubstituting Formats to Avoid ErrorsExampleCreating Formats from SAS Data SetsExampleRules for Control Data SetsExampleStep 1: List Data Set VariablesStep 2: Restructure the DataApply the FormatCreating SAS Data Sets from Custom FomatsExampleStep 1: Create a SAS Data Set from the FormatStep 2: Edit the Data SetStep 3: Create a Format from the SAS Data SetSummaryText SummaryCreating Custom Formats Using the VALUE StatementCreating Formats with Overlapping RangesCreating Custom Formats Using the PICTURE StatementDocumenting FormatsManaging FormatsUsing Custom FormatsCreating Formats from SAS Data SetsCreating SAS Data Sets from FormatsSyntaxSample ProgramsCreating a Multilabel FormatCreating a Picture FormatCreating a Picture Format Using DirectivesRestructuring a SAS Data Set and Creating a Format from the DataCreating a SAS Data Set from a FormatPoints to RememberQuiz
18. Modifying SAS Data Sets and Tracking Changes
OverviewIntroductionObjectivesUsing the MODIFY StatementModifying All Observations in a SAS Data SetExampleModifying Observations Using a Transaction Data SetExampleHandling Duplicate ValuesHandling Missing ValuesModifying Observations Located by an IndexExampleHandling Duplicate ValuesControlling the Update ProcessExampleMonitoring I/O Error ConditionsUsing _IORC_ with %SYSRCExampleUnderstanding Integrity ConstraintsGeneral Integrity ConstraintsReferential Integrity ConstraintsPlacing Integrity Constraints on a Data SetExampleHow Constraints Are EnforcedExampleCopying a Data Set and Preserving Integrity ConstraintsDocumenting Integrity ConstraintsExampleRemoving Integrity ConstraintsExampleUnderstanding Audit TrailsInitiating and Reading Audit TrailsExampleReading Audit Trail FilesExamplesControlling Data in the Audit TrailData Set VariablesAudit Trail VariablesValues of the _ATOPCODE_ VariableUsing the LOG Statement to Control the Data in the Audit TrailExampleUser VariablesExampleControlling the Audit TrailExampleUnderstanding Generation Data SetsInitiating Generation Data SetsExampleCreating Generation Data SetsProcessing Generation Data SetsExamplesHow Generation Numbers ChangeExamplesSummaryText SummaryUsing the MODIFY StatementModifying All Observations in a SAS Data SetModifying Observations Using a Transaction Data SetModifying Observations Located by an IndexControlling the Update ProcessPlacing Integrity Constraints on a Data SetDocumenting and Removing Integrity ConstraintsInitiating and Terminating Audit TrailsControlling Data in the Audit TrailInitiating Generation Data SetsProcessing Generation Data SetsSyntaxModifying All Observations in a SAS Data SetModifying a Master Data Set Using the BY StatementModifying a Master Data Set Using a Transaction Data Set and an IndexControlling the Update ProcessUsing PROC DATASETS to Create Integrity Constraints, Generation Data Sets, and Audit TrailsUsing _IORC_ with %SYSRCSpecifying an Audit Trail FileUsing PROC DATASETS to Rename or Delete Generation Data SetsSample ProgramsModifying a Data Set Using the MODIFY Statement with a BY Statement and with the KEY= OptionPlacing Integrity Constraints on DataInitiating an Audit TrailInitiating Generation Data SetsPoints to RememberQuiz
4. Optimizing SAS Programs
19. Introduction to Efficient SAS Programming
OverviewIntroductionObjectivesOverview of Computing ResourcesAssessing Efficiency Needs at Your SiteAssessing Your Technical EnvironmentAssessing Your ProgramsAssessing Your DataUnderstanding Efficiency Trade-offsUsing SAS System Options to Track ResourcesUsing Benchmarks to Compare TechniquesGuidelines for BenchmarkingSummaryOverview of Computing ResourcesAssessing Efficiency Needs at Your SiteUnderstanding Efficiency Trade-offsUsing SAS System Options to Track ResourcesUsing Benchmarks to Compare Techniques
20. Controlling Memory Usage
OverviewIntroductionObjectivesPrerequisitesControlling Page Size and the Number of BuffersMeasuring I/OPage SizeReporting Page SizeUsing the BUFSIZE= OptionUsing the BUFNO= OptionComparative Example: Using the BUFSIZE= Option and the BUFNO= OptionProgramming TechniquesGeneral RecommendationsUsing the SASFILE StatementGuidelines for Using the SASFILE StatementComparative Example: Using the SASFILE StatementProgramming TechniquesGeneral RecommendationsAdditional FeaturesUsing the IBUFSIZE= System OptionSummaryControlling Page Size and the Number of BuffersUsing the SASFILE StatementAdditional FeaturesQuiz
21. Controlling Data Storage Space
OverviewIntroductionObjectivesPrerequisitesReducing Data Storage Space for Character VariablesHow SAS Assigns Lengths to Character VariablesReducing the Length of Character Data with the LENGTH StatementOther TechniquesReducing Data Storage Space for Numeric VariablesHow SAS Stores Numeric VariablesAssigning Lengths to Numeric VariablesExampleMaintaining Precision in Reduced-Length Numeric VariablesUsing PROC COMPAREExampleComparative Example: Creating a SAS Data Set That Contains Reduced-Length Numeric VariablesProgramming TechniquesGeneral RecommendationsCompressing Data FilesReview of Uncompressed Data File StructureCompressed Data File StructureDeciding Whether to Compress a Data FileThe COMPRESS= System Option and the COMPRESS= Data Set OptionExampleAccessing Observations Directly in a Compressed Data SetThe POINTOBS= Data Set OptionExampleThe REUSE= System Option and the REUSE= Data Set OptionExampleHow SAS Compresses DataComparative Example: Creating and Reading Compressed Data FilesProgramming TechniquesGeneral RecommendationsUsing SAS DATA Step Views to Conserve Data Storage SpaceDATA Step ViewsExampleThe DESCRIBE StatementCreating and Referencing a SAS DATA Step ViewReferencing a Data View Multiple Times in One ProgramExampleMaking Multiple Passes Through Data in a Data ViewCreating Data Views on Unstable DataComparative Example: Creating and Reading a SAS Data ViewProgramming TechniquesGeneral RecommendationsSummaryReducing Data Storage Space for Character VariablesReducing Data Storage Space for Numeric VariablesCompressing Data FilesUsing SAS DATA Step Views to Conserve Data Storage SpaceQuiz
22. Using Best Practices
OverviewIntroductionObjectivesPrerequisitesExecuting Only Necessary StatementsPositioning of the Subsetting IF StatementComparative Example: Creating a Subset of DataProgramming TechniquesGeneral RecommendationsUsing Conditional Logic EfficientlyComparative Example: Creating Variables Conditionally Using DO GroupsProgramming TechniquesGeneral RecommendationsComparative Example: Creating Variables Conditionally When Calling FunctionsProgramming TechniquesGeneral RecommendationsUsing DO Groups EfficientlyComparative Example: Creating Data in DO GroupsProgramming TechniquesGeneral RecommendationsEliminating Unnecessary Passes through the DataUsing a Single DATA or PROC Step to Enhance EfficiencyUsing a Single DATA Step to Create Multiple Output Data SetsUsing the SORT Procedure with a WHERE Statement to Create Sorted SubsetsUsing the DATASETS Procedure to Modify Variable AttributesComparative Example: Creating Multiple Subsets of a SAS Data SetProgramming TechniquesGeneral RecommendationsComparative Example: Creating a Sorted Subset of a SAS Data SetProgramming TechniquesGeneral RecommendationsComparative Example: Changing the Variable Attributes of a SAS Data SetProgramming TechniquesGeneral RecommendationsReading and Writing Only Essential DataSelecting Observations Using Subsetting IF versus WHERE StatementComparative Example: Creating a Subset of a SAS Data SetProgramming TechniquesGeneral RecommendationsOther Differences between the IF and WHERE StatementsUsing the WHERE Statement with the OBS= and FIRSTOBS= OptionsSelecting Observations When Reading Data from External FilesComparative Example: Creating a Subset of Data by Reading Data from an External FileProgramming TechniquesGeneral RecommendationsSubsetting Variables with the KEEP= and DROP= Statements and OptionsComparative Example: Creating a Report That Contains Average and Median StatisticsProgramming TechniquesGeneral RecommendationsComparative Example: Creating a SAS Data Set That Contains Only Certain VariablesProgramming TechniquesGeneral RecommendationsStoring Data in SAS Data SetsComparative Example: Creating a SAS Data Set or Reading from an External FileProgramming TechniquesGeneral RecommendationsAvoiding Unnecessary Procedure InvocationExecuting the DATASETS ProcedureRUN-Group ProcessingUsing Different Types of RUN Groups with PROC DATASETSComparative Example: Modifying the Descriptor Portion of SAS Data SetsProgramming TechniquesGeneral RecommendationsSummaryExecuting Only Necessary StatementsEliminating Unnecessary Passes Through the DataReading and Writing Only Essential DataStoring SAS Data in SAS Data SetsAvoiding Unnecessary Procedure InvocationQuiz
23. Selecting Efficient Sorting Strategies
OverviewIntroductionObjectivesPrerequisitesAvoiding Unnecessary SortsUsing BY-Group Processing with an IndexComparative Example: Using BY-Group Processing with an Index to Avoid a SortProgramming TechniquesGeneral RecommendationsUsing the NOTSORTED OptionExampleUsing FIRST. and LAST.ExampleUsing the GROUPFORMAT OptionExampleUsing the CLASS StatementExampleComparative Example: Using a BY or CLASS Statement to Avoid a SortProgramming TechniquesGeneral RecommendationsUsing the SORTEDBY= Data Set OptionExampleUsing a Threaded SortUsing the CPUCOUNT= System OptionCalculating and Allocating Sort ResourcesSort Space RequirementsExampleUsing the SORTSIZE= OptionHandling Large Data SetsDividing a Large Data SetComparative Example: Dividing and Sorting a Large Data Set 1Programming TechniquesGeneral RecommendationsComparative Example: Dividing and Sorting a Large Data Set 2Programming TechniquesGeneral RecommendationsUsing the TAGSORT OptionExampleRemoving Duplicate Observations EfficientlyUsing the NODUPKEY OptionExampleUsing the NODUPRECS OptionExampleUsing the EQUALS | NOEQUALS OptionExampleUsing FIRST. LAST. Processing in the DATA StepComparative Example: Removing Duplicate Observations EfficientlyProgramming TechniquesComparing Techniques to Eliminate Duplicate DataGeneral RecommendationsAdditional FeaturesSelecting a Host Sort UtilityUsing the SORTPGM= System OptionUsing the SORTCUTP= System OptionUsing the SORTCUT= System OptionUsing the SORTNAME= System OptionExampleSummaryAvoiding Unnecessary SortsUsing a Threaded SortCalculating and Allocating Sort ResourcesHandling Large Data SetsRemoving Duplicate Observations EfficientlyAdditional FeaturesQuiz
24. Querying Data Efficiently
OverviewIntroductionObjectivesPrerequisitesUsing an Index for Efficient WHERE ProcessingAccessing Data SequentiallyExampleAccessing Data DirectlyExampleBenefits and Costs of Using an IndexHow SAS Selects an Access MethodIdentifying Available IndexesExample: Identifying One Available IndexExample: Identifying Multiple Available IndexesCompound OptimizationExample: Composite Index That Can Be Used to Optimize Multiple ConditionsExample: Composite Index That Can Be Used to Optimize One ConditionExample: Composite Index That Cannot Be Used for OptimizingIdentifying Conditions That Can Be OptimizedRequirements for Optimizing a Single WHERE ConditionWHERE Conditions That Cannot Be OptimizedRequirements for Compound OptimizationExample: Compound OptimizationEstimating the Number of ObservationsPrinting Centile InformationExampleComparing Probable Resource UsageHow SAS Compares Resource UsageFactors That Affect I/OSubset Size Relative to Data Set SizeNumber of Pages in the Data FileOrder of the DataCost to Uncompress a Compressed File for a Sequential ReadOther Factors That Affect Resource UsageDeciding Whether to Create an IndexGuidelines for Deciding Whether to Create an IndexExample: Selecting Subsets of Various Sizes from Data Sets of Various SizesQuery 1: Small Subset from a Large Data SetQuery 2: Large Subset from a Large Data SetQuery 3: Small Subset from a Small Data SetUsing the Options IDXWHERE= and IDXNAME= to Control Index UsageSpecifying MSGLEVEL=I to Determine Whether SAS Is Using an IndexExample: Using IDXWHERE=NO to Prevent Index UsageComparing Procedures That Produce Detail ReportsExample: Using PROC PRINT and PROC SQL to Create Detail ReportsReport 1: Simple Detail ReportReport 2: Subset Detail ReportReport 3: Sorted Detail ReportReport 4: Sorted Subset Detail ReportComparing Tools for Summarizing DataComparing Resource Usage across Summarization ToolsComparative Example: Displaying Summary Statistics for One Class VariableProgramming TechniquesGeneral RecommendationsUsing PROC MEANS to Display Summary Statistics for Combinations of Class VariablesComparing Resource Usage across Three Techniques for Using PROC MEANSUsing a Basic PROC MEANS Step to Combine All Class VariablesExample: Displaying Summary Statistics for All Combinations of the Class VariablesUnderstanding TypesUsing the TYPES Statement in PROC MEANS to Combine Class VariablesExample: Using the TYPES Statement in PROC MEANSUsing the NWAY Option in PROC MEANS to Combine Class VariablesExample: Using the NWAY Option in Multiple PROC MEANS StepsUsing the WHERE= Option in PROC MEANS to Combine Class VariablesExample: Using the WHERE= Option in PROC MEANSComparative Example: Displaying Summary Statistics for Combinations of Class VariablesProgramming TechniquesGeneral RecommendationsAdditional FeaturesSummaryUsing an Index for Efficient WHERE ProcessingIdentifying Available IndexesIdentifying Conditions That Can Be OptimizedEstimating the Number of ObservationsComparing Probable Resource UsageDeciding Whether to Create an IndexComparing Procedures That Produce Detail ReportsComparing Tools for Summarizing DataQuiz
5. Quiz Answer Keys
1. Quiz Answer Keys
Chapter 1: Performing Queries Using PROC SQLChapter 2: Performing Advanced Queries Using PROC SQLChapter 3: Combining Tables Horizontally Using PROC SQLChapter 4: Combining Tables Vertically Using PROC SQLChapter 5: Creating and Managing Tables Using PROC SQLChapter 6: Creating and Managing Indexes Using PROC SQLChapter 7: Creating and Managing Views Using PROC SQLChapter 8: Managing Processing Using PROC SQLChapter 9: Introducing Macro VariablesChapter 10: Processing Macro Variables at Execution TimeChapter 11: Creating and Using Macro ProgramsChapter 12: Storing Macro ProgramsChapter 13: Creating Samples and IndexesChapter 14: Combining Data VerticallyChapter 15: Combining Data HorizontallyChapter 16: Using Lookup Tables to Match DataChapter 17: Formatting DataChapter 18: Modifying SAS Data Sets and Tracking ChangesChapter 19: Introduction to Efficient SAS ProgrammingChapter 20: Controlling Memory UsageChapter 21: Controlling Data Storage SpaceChapter 22: Using Best PracticesChapter 23: Selecting Efficient Sorting StrategiesChapter 24: Querying Data Efficiently

Content preview from SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition

About This Book

Purpose

The SAS Certification Prep Guide: Advanced Programming for SAS®9 prepares you to take the SAS Advanced Programming exam for SAS®9. New and experienced SAS users who want to prepare for this exam will find this guide to be an invaluable, convenient, and comprehensive resource that covers all of the topics tested on the exam.

Major topics include SQL processing with SAS, the SAS macro language, advanced SAS programming techniques, and optimizing SAS programs. You will also become familiar with the enhancements and new functionality that are available in SAS®9.

The book includes quizzes that enable you to test your understanding of material in each chapter. Additionally, solutions to all quizzes are included at the back of the ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition

Publisher Resources

ISBN: 9781607640448

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition

by SAS Institute

About This Book

Purpose

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.