prg1c06 - 1 Chapter 6: Reading Excel Worksheets 6.1 Using...

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Chapter 6: Reading Excel Worksheets 6.1 Using Excel Data as Input 6.2 Doing More with Excel Worksheets (Self-Study) 2 Chapter 6: Reading Excel Worksheets 6.1 Using Excel Data as Input 6.1 Using Excel Data as Input 6.2 Doing More with Excel Worksheets (Self-Study) 3 Objectives Use the DATA step to create a SAS data set from an Excel worksheet. Use the SAS/ACCESS LIBNAME statement to read from an Excel worksheet as though it were a SAS data set. 3 4 Business Scenario An existing data source contains information on Orion Star sales employees from Australia and the United States. A new SAS data set needs to be created that contains a subset of this existing data source. This new SAS data set must contain the following: only the employees from Australia who are Sales Representatives the employee's first name, last name, salary, job title, and hired date labels and formats in the descriptor portion 4 5 Business Scenario 5 Reading SAS Data Sets Reading Excel Worksheets Reading Delimited Raw Data Files 6 Business Scenario 6 Reading SAS Data Sets Reading Excel Worksheets Reading Delimited Raw Data Files data ; infile ; input ; ... run; libname ; data ; set ; ... run; libname ; data ; set ; ... run; 7 Business Scenario Syntax Use the following statements to complete the scenario: 7 LIBNAME libref ' physical-file-name ' ; DATA output-SAS-data-set ; SET input-SAS-data-set ; WHERE where-expression ; KEEP variable-list ; LABEL variable = ' label ' variable = ' label ' variable = ' label ' ; FORMAT variable(s) format ; RUN; LIBNAME libref ' physical-file-name ' ; DATA output-SAS-data-set ; SET input-SAS-data-set ; WHERE where-expression ; KEEP variable-list ; LABEL variable = ' label ' variable = ' label ' variable = ' label ' ; FORMAT variable(s) format ; RUN; 8 sales.xls 8 two worksheets cells formatted as dates 9 The LIBNAME Statement (Review) The LIBNAME statement assigns a library reference name (libref) to a SAS data library. General form of the LIBNAME statement: Example: 9 libname orion 's:\workshop'; LIBNAME libref ' SAS-data-library ' <options> ; LIBNAME libref ' SAS-data-library ' <options> ; physical location of SAS data library libref 10 The SAS/ACCESS LIBNAME Statement The SAS/ACCESS LIBNAME statement extends the LIBNAME statement to support assigning a library reference name (libref) to Microsoft Excel workbooks. General form of the SAS/ACCESS LIBNAME statement: This enables you to reference worksheets directly in a DATA step or SAS procedure, and to read from and write to a Microsoft Excel worksheet as though it were a SAS data set. 10 LIBNAME libref ' physical-file-name ' < options > ; LIBNAME libref ' physical-file-name ' < options > ; 11 The SAS/ACCESS LIBNAME Statement SAS/ACCESS Interface to PC File Formats is required in order to use the SAS/ACCESS LIBNAME statement to access Excel workbooks....
View Full Document

Page1 / 60

prg1c06 - 1 Chapter 6: Reading Excel Worksheets 6.1 Using...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online