You use SAS Your boss uses Excel Guess
You use SAS®, Your boss uses Excel, Guess where your results are going to appear! (Part 2): DIGGING DEEPER INTO ODS to put your SAS data and results into Excel. William E Benjamin Jr, Owl Computer Consultancy, LLC, Phoenix, Arizona Copyright © 2010, Meta-Xceed, Inc. All rights reserved. BI Flash and all other Meta-Xceed Inc. product or service names are registered trademarks or trademarks of Meta-Xceed Inc. in the USA and other countries. ® indicates USA registration.
Agenda n The Problem - Moving Data to Excel Using ODS n Early Methods – DDE, CSV and others n A New Method – ODS and Tagsets • What is a “Tagset” ? • How do You find a Tagset • How does a Tagset Work?
Agenda n Focus - SAS Data to Excel via Excel. XP Tagset • Getting the new SAS updated Excel. XP • Installing the Excel. XP Tagset • Understanding the Excel. XP tagset
Agenda Using the Excel. XP Tagset − Hello World Project − Tagset options − Style Commands − Using SAS Vars as Excel Formulas − SAS Can Send Hyperlinks to Excel − Create Multi-Sheet workbooks − Table of Contents – Click to Your Desired Page Conclusion
The Problem – Data to Excel n Most Company PC’s have Excel Installed n Many Company Programmers/Analysts use SAS • Programmers Collect and Report the Data n Management Often Uses Excel • Managers Use and Explain the Results Within Excel
The Problem – Data to Excel n Many Ways to Convert from SAS to Excel n Dissimilar Results from Different Methods n Most Transfer Methods Require Manual Excel Editing to produce final results
An Early Method – DDE n One of the first transfer methods n Available from the SAS Data step n Based Upon Microsoft Standards n Time intensive to Setup for use n Cell by Cell control of Excel results n Not Available in SAS® Enterprise Guide n Subject of a Previous Paper
An Early Method – CSV n One of the first transfer methods n Available from the SAS Data step n One of the first ODS output destinations n Simple to use n Limited control of Excel results n May Require Manual Formatting in Excel
An Early Method – CSV PROC EXPORT DATA=SASHELP. SHOES OUTFILE="C: wuss_2010Shoes_1 a. csv" DBMS=CSV REPLACE; Run;
An Early Method – CSV Here is a sample of the output file: "Obs", "Region", "Product", "Subsidiary", "Stores", "Sales", "Inventory", "Returns" " 1", "Africa", "Boot", "Addis Ababa", "12", " $29, 761", " $191, 821", " $769" " 2", "Africa", "Men's Casual", "Addis Ababa", " 4", " $67, 242", " $118, 036", " $2, 284" " 3", "Africa", "Men's Dress", "Addis Ababa", " 7", " $76, 793", " $136, 273", " $2, 433“ - - More data – "393", "Western Europe", "Sport Shoe", "Rome", "14", " $9, 969", " $74, 848", " $549" "394", "Western Europe", "Women's Casual", "Rome", " 2", " $19, 964", " $62, 256", " $954"
An Early ODS Method – CSV ODS CSV body='f: WUSS_2010Shoes_1 a. csv'; PROC PRINT DATA=SASHELP. SHOES ; RUN; ODS CSV CLOSE;
An Early ODS Method – CSV Here is a sample of the output file: "Obs", "Region", "Product", "Subsidiary", "Stores", "Sales", "Inventory", "Returns" " 1", "Africa", "Boot", "Addis Ababa", "12", " $29, 761", " $191, 821", " $769" " 2", "Africa", "Men's Casual", "Addis Ababa", " 4", " $67, 242", " $118, 036", " $2, 284" " 3", "Africa", "Men's Dress", "Addis Ababa", " 7", " $76, 793", " $136, 273", " $2, 433“ - More data – "393", "Western Europe", "Sport Shoe", "Rome", "14", " $9, 969", " $74, 848", " $549" "394", "Western Europe", "Women's Casual", "Rome", " 2", " $19, 964", " $62, 256", " $954"
A New Method - ODS & Tagsets
A New Method - ODS & Tagsets What is a “Tagset” ?
A New Method - ODS & Tagsets A “Tagset” is a “Mediator”
A New Method - ODS & Tagsets SAS Code ODS TAGSET XML/HTLM/etc Excel/Word/PDF/etc
A New Method - ODS & Tagsets How do You find a Tagset
A New Method - ODS & Tagsets
A New Method - ODS & Tagsets
A New Method - ODS & Tagsets
A New Method - ODS & Tagsets
A New Method - ODS & Tagsets
A New Method - ODS & Tagsets How does a Tagset Work?
A New Method - ODS & Tagsets SAS Code or Procedure Sends Data to ODS Uses a Tagset to Direct / Encode Outputs are Formatted (XLM/HTML/Others) Target Programs Analyze the Outputs Target Programs Visualize the Data Target Programs Include Excel, Word, Adobe, Browsers
A New Method - ODS & Tagsets W or d CE L se r F ow </Body> s Proc Br Footnote PD Title <Style ss: ID="parskip" ss: Parent="body"> <Alignment/> <ss: Borders> <ss: Border ss: Position="Left" /> <ss: Border ss: Position="Top" /> <ss: Border ss: Position="Right" /> <ss: Border ss: Position="Bottom" /> </ss: Borders> <Protection ss: Protected="1" /> </Style> </Row> <Row ss: Auto. Fit. Height="1" ss: Height="15"> <Cell ss: Style. ID="data__r" ss: Index="1"><Data ss: Type="Number">1</Data></Cell> <Cell ss: Style. ID="data__r" ss: Index="2"><Data ss: Type="Number">2</Data></Cell> <Cell ss: Style. ID="data__l" ss: Index="3"><Data ss: Type="Number">3</Data></Cell> </Row> S/ ODS <Header> </Header> <Body> EX SAS Code XML/HTML M Data T A G S E T
SAS Data to Excel via Excel. XP • Getting the new SAS updated Excel. XP http: //support. sas. com/rnd/base/odsmarkup/excltags. tpl As of October 16, 2010 the version available was SAS 9. 1. 3 Compatible, v 1. 116, 08/25/10
SAS Data to Excel via Excel. XP Installing the Excel. XP Tagset • Save Tagset file somewhere to load into the SAS Editor • Load the Tagset code into the SAS editor (NOTE * You Need the ability to write to SASUSER libname) • Execute the Tagset code by any way you want. (Choose a “RUN” option, PF Key, or click the “Running Man Icon”)
SAS Data to Excel via Excel. XP
SAS Data to Excel via Excel. XP SASUSER and SASHELP May both be writable Only write into SASUSER Path order is Important
SAS Data to Excel via Excel. XP Understanding the Excel. XP tagset n Start by Executing the following SAS Program: ODS tagsets. excelxp file="test. xml" options(doc="help"); Run;
Understanding the Excel. XP tagset n The Results Will be Something Like This A Listing Will be Produced followed by a message (the listing may be at least an 8 page printed help file): ============================= NOTE: This is the Excel XP tagset (Compatible with SAS 9. 1. 3 and above, v 1. 116, 08/25/10). Add options(doc='help') to the ods statement for more information.
READ THE INSTRUCTIONS !
Understanding the Excel. XP tagset ?
Understanding the Excel. XP tagset n Tagset Code does not run sequentially n Tagset Code Units are Defined n Tagset Code Units are Triggered n Tagset Code Units are Similar to Macros n Tagset Code Units are Magical n If You do not write them, …. THAT IS ALL YOU NEED TO KNOW
Using the Excel. XP Tagset Declare You want to use the Excel. XP Tagset ods tagsets. excelxp file=‘c: my_file. xls’;
Using the Excel. XP Tagset Let’s start simple: The Hello World Project… (Shamelessly borrowed directly from the Excel. XP Tagset Help output listing…)
Hello World Project *Get Started – Open a Tagset and an Excel file (*. xml); ods tagsets. excelxp file='test 1. xml' options(zoom='75');
Hello World Project data test; length a b 8 c $20; input a b c $; cards; 123 2 3 =RC[-2]+RC[-1] 3 4 =RC[-2]+RC[-1]. . =SUM(R[-3]C: R[-1]C) run;
Hello World Project * Option added to hide spreadsheet periods; Options missing = ‘ ‘; proc print noobs; var a b; var c / style(head) = {flyover="Hello World"} style(data) = {cellwidth=50 pt}; run;
Hello World Project * Now end the job; ods tagsets. excelxp close; run; * added to reset the default missing display; Options missing = ‘. ’;
Hello World Project
Hello World Project
Tagset options Let us examine some simple tagset options 1. 2. 3. 4. zoom='75' sheet_interval='bygroup' sheet_label='By ' row_heights='40'
Tagset options ods tagsets. excelxp file='test 3. xml' options(zoom='75' sheet_interval='bygroup' sheet_label='By ' row_heights= '40');
Tagset options n Lets get some simple data(straight from SAS); proc sort data=sashelp. shoes out=tmp; by Region Product Sales; run;
Tagset options Now Print the sorted file; proc print data=tmp noobs; by region ; sum sales; var product; var sales / style(data) = {cellwidth=50 pt }; Run;
Tagset options * Now quit; ods tagsets. excelxp close; run;
Tagset options
Style Commands SAS 9 Reporting Procedure Styles Tip Sheet
Style Commands One Simple command: var sales / style(data) = {cellwidth=50 pt };
Using SAS Vars as Excel Formulas Data converted to a formula: Remember this data: =SUM(R[-3]C: R[-1]C) In Excel you saw: 15 Which really was in Excel-eaze =sum(c 2: c 4)
Using SAS Vars as Excel Formulas Everything is relative to the current cell! 15 was in cell ‘c 5’ So… =SUM(R[-3]C: R[-1]C) Means add the column of cells in the current row minus 3, and the current column; until the current row minus 1, and the current column. Then put the result here. (3+5+7=15)
SAS Can Send Hyperlinks to Excel ods tagsets. excelxp file='Z: WUSS_2010test 5. xml' style =Gears ; proc sort data=sashelp. shoes out=shoes 1; by Region Product Subsidiary Sales ; run;
SAS Can Send Hyperlinks to Excel *remove spaces, special chars from var used as sheet name (Primary By variable); data shoes 1; set shoes 1; x = 1; do until (x eq 0); x = index(left(trim(region)), ' /') ; region = translate(left(trim(region)), '_', ' '); region = translate(region, '_', '/'); end; drop x; run;
SAS Can Send Hyperlinks to Excel ods tagsets. excelxp options (sheet_label = 'Table of contents') ; title 1 'Worldwide Show Sales by Region‘; data comments (keep=comments); retain counter 1; attrib comments length = $ 90; comments = '______________'; output; comments = 'This Excel Workbook contains comments = 'several sheets with one for comments = 'each region of the world '; output; comments = '______________'; output; comments = ' Regions displayed '; output; comments = '______________'; output; comments = 'Left Click title to see data '; output; comments = '______________'; output;
SAS Can Send Hyperlinks to Excel * output one record for each region; do until (eof); set shoes 1 end=eof; by region; if first. region then do; counter + 1; *Build an Excel hyperlink to a new page in same workbook; comments = '=HYPERLINK("[test 5. xml]' || left(trim(put(region, $char 30. ))) || '!A 1", "' || left(trim(put(region, $char 30. ))) '")'; output; end;
SAS Can Send Hyperlinks to Excel * write text at bottom of table of contents page; comments = '______________'; output; comments = ' CAUTION - CHANGING THE '; output; comments = ' NAME OF THE WORKBOOK '; output; comments = ' MAY INVALIDATE THE '; output; comments = ' HYPERLINKS IN THE FILE '; output; comments = '______________'; output; stop; proc print data=comments noobs label; run;
Create Multi-Sheet workbooks ods tagsets. excelxp options(zoom='75' sheet_interval='bygroup' sheet_label=' ' row_heights= '40, 20' Gridlines= 'Yes ') ; proc print data=shoes 1 noobs; by region ; sum sales; var product Subsidiary / style(data) = {font_size=14 pt}; var sales / style(data) = {cellwidth=50 pt font_size=12 pt}; run;
Create Multi-Sheet workbooks ********************************; ** Close up and go home. **; ********************************; ods tagsets. excelxp close; title; run;
-- Table of Contents -Click to Your Desired Page
-- Table of Contents -Click to Your Desired Page
-- Table of Contents -Click to Your Desired Page
Conclusion WOW
Conclusion William E Benjamin Jr Owl Computer Consultancy, LLC Phoenix AZ William@Owl. Computer. Consultancy. com
- Slides: 64