ProBBQ Excel 20 Bedienungsanleitung

1Choosing the Right Tool from Your SAS®and Microsoft Excel®Tool Belt2997 Yarmouth Greenway Drive, Madison, WI 53711Phone: (608) 278-9964 • Web: www.s

10What About Excel Capabilities?• Excel is a much more interactive and simple to use tool. • It is very easy to use and learn.• Users can begin using

11Are SAS and Excel Structures Similar?Shapes of SAS, Excel data usually close enough to make transfer easy:• SAS datafiles, Excel worksheets are both

12What Are The SAS and Excel Structure Differences?SAS:• has a required dataset descriptor( mini data dictionary) defining columns. • columns are name

13Do SAS and Excel Use “Libraries”?SAS• Stores its data in SAS libraries. • Libraries are roughly the equivalent of a Windows or Unix directory.• Eac

14How Can SAS Send Information to Excel?Before we can do any transfer, are we talking about:• A SAS dataset?Or• A SAS report? We can do both!! Choosi

15How About Sending a SAS Report to Excel?In the past:• PROC PRINTTO could capture print.• Separately we could parse the report to create suitable inp

16Can You Show us an Example?Suppose we write the following proc tabulate code to produce a report:proc tabulate data=softsale;title 'Softsale Sa

17Can We See the Output?Softsale Sales and Expenses by Division

18So How Do We Get it to Excel?We can easily route the output to HTML with a few ODS statements.ods html body='c:\temp\tabulate.html';proc t

19How Does That Help Us Get to Excel?Easy! To open the file in Excel, use File Open and specify file type HTML. Choosing the Right Tool from Your SAS

2This paper was written by Systems Seminar Consultants, Inc.SSC specializes in SAS software and offers:• Training Services• Consulting Services• He

20What Does Excel Do with the HTML File?• Excel automatically converts HTML as it reads the file.• The user may have to specify to save it as an XLS f

21Can We Make This Process More Transparent?Yes!• Continue to generate HTML but naming our file with .XLS. • Excel and Windows are tricked to think t

22Can We See the Code?options nocenter;ods html body='c:\temp\tabulate.xls' style=minimal;proc tabulate data=softsale;. . .run; ods html clo

23Are There Differences in Formatting?Excel may format things differently than SAS does: • Leading zeros that don‟t appear in Excel. • Text doesn‟t s

24Is There a More Controlled Way to Send Data to Excel?ODS Markup Family of Destinations:• Designed to create markup codes for easy input by other sof

25Are the Tagsets Documented?• SAS.COM• Online Help• Run the following Program:ods tagsets.excelxp file='test.xml' options(doc='help&ap

26Can We See an Example Using EXCELXP?An example of a simple PROC PRINT using EXCELXP follows: ods tagsets.excelxp file='c:\temp\test.xml';

27What Does The Worksheet Look Like?• The Worksheet looks like the report.• Titles don‟t appear, as they are instead used as print headers.• SAS will

28Can I Include Titles and Specify the Sheet Name?Options are available:• Set the worksheet name prefix. • SAS will use to create a separate worksheet

29The Resulting WorksheetTitles are imbedded and each state will is in a separate tab.Choosing the Right Tool from Your SAS®and Microsoft Excel®Tool

3Free SAS NewsletterThe Missing Semicolon™, shares SAS software solutions developed by our staff and provides additional technical assistance to our c

30While Reporting is Great, Can We Copy Data?• There are a wealth of ways to copy SAS data to Excel.• We can eliminate the reporting step.• Some requi

31Can We Copy and Paste From SAS?• Copying and pasting all the cells from a SAS dataset in the SAS VIEWTABLE window is not supported.• A single cell c

32What is the SAS System Viewer?• A free Windows application to view SAS data.• Download it from The SAS Institute at: http://www.sas.com/apps/demosdo

33Can You Show Copy and Paste In the System Viewer?In the SAS System Viewer:• Open the data set you want to send to Excel. • Select all of the rows a

34Can We then Paste Into Excel?In Excel:• Use the Windows shortcut to paste (Ctrl+V).• You should be able to paste all of your data into Excel.• You m

35Can You Copy and Paste From Enterprise Guide?In Enterprise Guide:• Open your dataset.• Highlight all of the rows and columns you want.• Edit menu an

36Can Data Step Programs Send SAS Data to Excel?A data step can create a comma separated value (CSV) text file.Features:• Wide capabilities of the dat

37Are There Drawbacks to a Data Step Program?• A Data step must be written.• You need know names of SAS columns for header rows.• You must also specif

38Can We See an Example?The following program creates a CSV file:data _null_;set softsale(obs=5);file 'c:\temp\test.csv' dsd;if _n_ =1 then

39What Does the CSV File Look Like?File 'c:\temp\test.csv„Name,Division,Years,Sales,Expense,StateBENJAMIN,S,3,201.11,25.21,ILJENNIFER,S,1,542.11,

Presenters44Steven J. FirstPresidentJennifer FirstDirector of Operations • Over 30 years of SAS experience, including hundreds of manufacturing, retai

40How Does It Look In Excel?Here is the Excel view after opening the above file.Choosing the Right Tool from Your SAS®and Microsoft Excel®Tool Belt

41Wouldn’t This Be a Great Application for a Macro?There are many user written macros to create CSV files. • %SSCFLAT macro reads dictionary tables to

42What is DDE?Dynamic Data Exchange (DDE) is a method of dynamically exchanging information between Windows applications. • Almost any command, input

43What is Needed to Start a DDE Server?• Excel must have an open worksheet before running the SAS code.• A DDE server app can be opened using the X co

44Can We See a Data Step That Writes to DDE?Example: send first 5 rows and selected columns from a SAS Excel. filename excelout dde 'excel|sheet1

45What Do the Results Look Like?A Worksheet in Excel.Choosing the Right Tool from Your SAS®and Microsoft Excel®Tool Belt

46What is the SAS/Access Interface to PC File Formats?With this additionally licensed product, transfer of data becomes even easier. The product cont

47How Does The Export Wizard Work?• A point-and-click interface.• Guides you through reading SAS data, writing to an external file format. • External

48How Do You Start the Export Wizard?• Open the Export Wizard, selecting File, Export Data, Select Library. • Member window opens, select Library

49And Then What Happens?• The Select Export Type window opens. • Select Export Type in the Export Wizard from the drop down list, Next. Choosing the

5Introduction• SAS software analytics, reporting, and programming are second to none.• Excel is probably the most popular software product used today.

50Where Do We Give an Excel Workbook Name?• The Connect to MS Excel dialog box opens, type or Browse as needed, then OK. Choosing the Right Tool from

51Can We Specify a Table Name (Named Range)?The Select Table window allows a name to be typed or pulled down.Choosing the Right Tool from Your SAS®and

52Can You Save the Generated SAS Code?• The Create SAS statements window opens.• We can pull down or type a file for storage and later running of SAS

53Can We See the Resulting Worksheet?The worksheet looks almost identical to our SAS dataset.Notes:• Many users think this is the easiest way to send

54What If I Like to Code, or Need Some Special Options?The Wizards either: • Generated DATA step code.• Generated SAS/ACCESS code.• Generated code usi

55Can We See the Code From Our Last Example?Here are the statements and arguments saved by the wizard: PROC EXPORT DATA= WORK.SOFTSALEOUTFILE= "c

56Advantages/Disadvantages of PROC EXPORT?Advantages:• PROC EXPORT is a straightforward conversion.• It has many options.• It is repeatable. Disadvan

57Is There a LIBNAME Engine to Excel?SAS/ACCESS for PC file formats does support LIBNAME.• Probably the most simple of all techniques.• LIBNAME assign

58What is a “Table” in the Excel Library?The SAS documentation deals with this topic as follows: • SAS/ACCESS treats an Excel workbook as a database.•

59Can We See an Example?For example:• Sheet1 is a sheet name in an Excel file. • SAS treats Sheet1$ as a valid range name.• SAS must use special namin

6Sending Excel to SAS• Most people are familiar with Excel.• Excel provides easy navigation.• Easy data input.• Formulas, formatting, much more availa

60What Can The LIBNAME Excel Engine Do?• Create new workbooks.• Create a new worksheet within a named range, write data to that range.• Write data to

61What Can The LIBNAME Excel Engine Not Do?• Rename worksheets in a workbook.• Delete worksheets or workbooks.• Change or apply formatting.• Delete ce

62Can Libname Write to a Worksheet?Libname can write to a worksheet. Example:• Write out a worksheet named “test”.• SAS will create a named range call

63Can LIBNAME Overwrite an Existing Worksheet?If the program is run again it would fail as the worksheet exists.We can precede it with a delete statem

64Can PROC SQL Write to a Worksheet?PROC SQL writes to a worksheet via CREATE TABLE. options noxwait;x 'del c:\temp\test.xls' noxwait;libnam

65Can Base SAS Read Excel Data? • SAS can also read data from Excel with a variety of methods.• Many almost identical (but opposite) techniques as alr

66Can We Copy and Paste to SAS DATALINES? • COPY and Paste is about as simple as it gets.• You can copy cells from Excel, paste after a DATALINES stat

67Can We See An Example? In Excel highlight the desired cells and issue the edit copy command (Ctrl C).Choosing the Right Tool from Your SAS®and Micro

68And The Paste Command in SAS data softsale;input Name $ Division $ Years Sales Expense State $;datalines;BENJAMIN S 3 201.11 25.21 ILJENNIFER S 1 5

69Can The DATA Step Read Directly From the Clipboard?The FILENAME CLIPBRD access reads directly from the Windows Clipboard (we can skip the paste step

7Sending SAS to Excel• Excel is de-facto report distribution system.• Can contain reports, graphs, and other SAS results in a familiar format. • Can

70Can Excel Create a CSV File?EXCEL‟s File Save As, choosing a file type of CSV. Name,Division,Years,Sales,Expense,StateBENJAMIN,S,3,201.11,25.21,ILJ

71Can SAS Read the CSV File? • The DSD INFILE option separates at the commas. • FIRSTOBS= option can ignore the header line.data softsale;infile &apo

72Can DDE And XML Read EXCEL DATA Within SAS? • DDE is supported for reading data in the DATA step.• It is very similar to what was shown earlier.• A

73What Are Reading Options with SAS/ACCESS?Reading Excel becomes much easier.• Import wizards• PROC IMPORT• LIBNAME • Even PROC SQL pass-through to re

74How Do PROC IMPORT Wizards Work?Wizards convert Excel to SAS and are very similar to those shown earlier. To start the process, use the File Impor

75How Do You Specify Excel as Input?You can select an import type of Excel and press Next.Choosing the Right Tool from Your SAS®and Microsoft Excel®To

76How Do We Name the Input Workbook?You can then fill in the workbook name, or browse to find it, press OK.Choosing the Right Tool from Your SAS®and M

77Can I Specify a Worksheet?Choose the sheet you would like to import from those shown.Choosing the Right Tool from Your SAS®and Microsoft Excel®Tool

78Where Do We Name Our OUTPUT?Choose a library and type in a member name.Choosing the Right Tool from Your SAS®and Microsoft Excel®Tool Belt

79Can the Generated Code Be Captured?You can capture the generated SAS PROC IMPORT for rerun, then Finish.Choosing the Right Tool from Your SAS®and Mi

8Some Questions about SAS and Excel• Are the structures similar?• How to transfer from SAS files to Excel and vice versa?• Are there formatting differ

80What Does the SAS Code Look Like?The PROC IMPORT can be altered and re-run at any time.PROC IMPORT OUT= WORK.SOFTSALE DATAFILE= "c:\temp\test.x

81Can We Read Excel Worksheets With Libname?• LIBNAME makes the workbook appear as a SAS library, and each “named range” appears as a table. • This is

82Do We Need to Set Up Named Ranges?You can, or use the ones SAS sets up.• LIBNAME automatically sets up range name (Sheet + $). • Can verify that wit

83How Do I Refer To The Table Name In SAS?• Use the name ranges as you would with any SAS dataset.• Because of the $ in the name enclose it in quotes,

84Can PROC SQL PASS-THROUGH Read Excel?• PROC SQL Pass-Through accesses Excel tables via sub-query.• Probably easiest when using Excel named ranges.

85Can I Export Excel Data From Enterprise Guide?• Exporting data from EG to Excel is very simple!• Go to your process flow, right click on the dataset

86Is There An Easy Way to Import Excel Into EG?• On the File menu, select Open → Data.• Select Local Computer or SAS Servers. • Navigate to the locati

87Can I Control the Import Settings in EG? • Select View the file as is to open the entire table and use default column headings, data types and forma

88What Are the Different Options?The Import Data Wizard allows you to choose:• Rows and columns to import.• The row to use for column headings.• Names

Can I Import a Specific Region From Excel?• On the Selection Pane of the Import Data Wizard , highlight Region to Import.• Change Region to Import opt

9What Are SAS Capabilities?A Small Subset:• SAS is excellent for analytics after data has been captured. • SAS is somewhat batch oriented.• SAS may r

What Are the Region Options?These options are available:• Choose a line to use as column headers.• Choose the starting line.• Choose the ending line.9

What About Setting Column Properties in EG?• On the Selection Pane, highlight Column Options.• On the Columns list, highlight the name of a column.• I

What Column Properties Can I Change?• Name: name of the variable. SAS naming restrictions apply.• Label: text that is sometimes displayed in place of

93What Is The SAS Add-In for Microsoft Office?• Part of SAS‟s comprehensive Enterprise Intelligence Platform.• Allows business users SAS capabilities

94What Can We Do With the SAS Add-In?Access Data• Utilize enterprise data from within MS Office.• Your SAS data sources. • Data sources on server (inc

95Can It Do Analysis and Reporting?• Analyze the data and return results back to Excel.• Programs or “SAS Stored Processes” execute SAS analytics and

96Conclusions• SAS provides a variety of tools to both read and write Excel.• Available products, source data, output requirements, data storage lim

97References and AcknowledgmentsREFERENCES • “More Tips and Tricks for Creating Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS”, DelGobbo

98Contact UsSAS®Training, Consulting, & Help Desk ServicesSteven [email protected] Yarmouth Greenway Drive • Madison, WI 5