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

Info icon This preview shows pages 1–13. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Chapter 6: Reading Excel Worksheets 6.1 Using Excel Data as Input 6.2 Doing More with Excel Worksheets (Self-Study)
Image of page 1

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

View Full Document Right Arrow Icon
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)
Image of page 2
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.
Image of page 3

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

View Full Document Right Arrow Icon
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
Image of page 4
5 Business Scenario Reading SAS Data Sets Reading Excel Worksheets Reading Delimited Raw Data Files
Image of page 5

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

View Full Document Right Arrow Icon
6 Business Scenario Reading SAS Data Sets Reading Excel Worksheets Reading Delimited Raw Data Files data ; infile ; input ; ... run; libname ; data ; set ; ... run; libname ; data ; set ; ... run;
Image of page 6
7 Business Scenario Syntax Use the following statements to complete the scenario: 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;
Image of page 7

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

View Full Document Right Arrow Icon
8 sales.xls two worksheets cells formatted as dates
Image of page 8
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: libname orion 's:\workshop'; LIBNAME libref ' SAS-data-library ' <options> ; LIBNAME libref ' SAS-data-library ' <options> ; physical location of SAS data library libref
Image of page 9

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

View Full Document Right Arrow Icon
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. LIBNAME libref ' physical-file-name ' < options > ; LIBNAME libref ' physical-file-name ' < options > ;
Image of page 10
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. Example: libname orionxls 's:\workshop\sales.xls'; physical file name of Excel workbook including path, filename, and extension libref p106d01
Image of page 11

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

View Full Document Right Arrow Icon
12 SAS Explorer Window Each worksheet in the Excel workbook is treated as though it is a SAS data set.
Image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern