/* TagSetExcelDDE.txt Frank Poppe PW Consulting the Netherlands 4dec2001 version 1.0 PURPOSE: Have ODS write your SAS output directly into Excel. HOW TO USE? (More comments and an example at the end of this file) DO ONCE: Make sure the ColorMac macro (in the standard SASAUTOS) is available and run it. Include the DDEmacros file which should accompany this TagsetDDE file. Give a list of colors (ColorRGBlist) as in the example a few lines down. Define a style with simple font names (again as in the example down here). AFTER THAT: Start up Excel. Define a fileref with DDE to Excel|System. Open ODS tagset.excel. Run any SAS proc. Close ODS. */ %ColorMac ( NOMSG ) ; %Include 'DDEmacros.sas' ; /* Give a list of colors in RGB triplets. Separate triplets with spaces, and within the triplet separate RGB values with commas. The values should be decimal between 0 and 255 (inclusive). The csrgb macro translates a SAS color name into such a value. Make sure the colors in your style are included. The following colors are the ones in the SAS default style, plus a few basic ones. */ %let ColorRGBList = 0,102,170 0,68,136 170,255,170 136,0,0 211,211,211 0,51,170 176,176,176 240,240,240 0,34,136 224,224,224 %csrgb(red) %csrgb(green) %csrgb(blue) %csrgb(yellow) %csrgb(black) %csrgb(white) ; proc template; /* First create a redefined style. Excel requires single font-names, not a list of names as in most styles. */ define style excel ; parent = styles.default ; replace fonts / 'TitleFont2' = ("Verdana",11pt,Bold Italic) 'TitleFont' = ("Verdana",12pt,Bold Italic) 'StrongFont' = ("Verdana",9pt,Bold) 'EmphasisFont' = ("Verdana",9pt,Italic) 'FixedEmphasisFont' = ("Courier New",8pt,Italic) 'FixedStrongFont' = ("Courier New",8pt,Bold) 'FixedHeadingFont' = ("Courier New",8pt,Bold) 'BatchFixedFont' = ("SAS Monospace",7pt) 'FixedFont' = ("Courier New",8pt) 'headingEmphasisFont' = ("Times",10pt,Bold Italic) 'headingFont' = ("Comic Sans MS",10pt, Bold) 'docFont' = ("Verdana",9pt); end; /* Define a simple Excel tagset*/ /* (In v8.2 be careful with trigger, this sometimes strange reordering of events This should be corrected in v9. */ define tagset Tagsets.Excel ; notes "An attempt to write to Excel through DDE, 15nov2001 "; define event table ; start: put '[select("R[2]C1")]' NL ; finish: put '[select("RC2:RC")]' NL ; put '[border( , , , , 2 )]' NL ; end ; define event colspecs ; put '[select("RC[1]:RC[' colcount ']")]' NL ; put '[border( , , , , 2 )]' NL ; end ; define event row ; start: put '[select("R[1]C1")]' NL ; put '[Border( , , 2 )]' NL ; finish: put '[Border( , , 2 )]' NL ; end; define event data; trigger spanned / if any ( rowspan , colspan ) ; trigger single / if ^ any ( rowspan , colspan ) ; end ; define event spanned ; /* select range to blank right and/or bottom borders*/ put '[Select("R[-1]C")]' NL ; put '[Select("R[1]C[1]:R' ; put '[' rowspan ']' / if exists ( rowspan ) ; put '[1]' / if ^exists ( rowspan ) ; put 'C' ; put '[' colspan ']' / if exists ( colspan ) ; put '[1]' / if ^ exists ( colspan ) ; put '")]' NL ; put '[Border( , , 0 , , 0 )]' NL ; put '[Apply.Style("SAS.' HTMLCLASS '")]' NL / if exists ( HTMLCLASS ) ; put '[formula("' VALUE '")]' NL / if exists ( value ) ; /* now select only last row to set the bottom border */ put '[Select("R[-1]C[-1]")]' NL ; put '[Select("R[' rowspan ']C[1]:' / if exists ( rowspan ) ; put '[Select("R[1]C[1]:' / if ^exists ( rowspan ) ; put 'R[' rowspan ']' / if exists ( rowspan ) ; put 'R[1]' / if ^exists ( rowspan ) ; put 'C[' colspan ']' / if exists ( colspan ) ; put 'C[1]' / if ^exists ( colspan ) ; put '")]' NL ; put '[Border( , , , , 1 )]' NL ; /* now select only last column to set right border */ /* first move back to one row one col before left corner */ put '[Select("R[-1]' / if ^exists ( rowspan ) ; put '[Select("R[-' rowspan ']' / if exists ( rowspan ) ; put 'C[-1]' ; put '")]' NL ; put '[Select("R[1]C[' colspan ']:R[' / if exists ( colspan ) ; put '[Select("R[1]C[1]:R[' / if ^exists ( colspan ) ; put rowspan / if exists ( rowspan ) ; put '1' / if ^ exists ( rowspan ) ; put ']C[' ; put colspan / if exists ( colspan ) ; put '1' / if ^ exists ( colspan ) ; put ']")]' NL ; put '[Border( , , 1 )]' NL ; put '[Select("RC")]' NL ; end; define event single ; put '[Select("RC[1]")]' NL / if ^ any ( colspan , rowspan ) ;; put '[Apply.Style("SAS.' HTMLCLASS '")]' NL / if exists ( HTMLCLASS ) ; put '[Select("RC")]' NL ; put '[Border( , , 1 , , 1 )]' NL ; put '[formula("' VALUE '")]' NL / if exists ( value ) ; end ; define event header ; /* same as data, but trigger is too risky in v8.2 */ trigger data ; end ; define event stylesheet ; /* because Excel needs the style info first, a new book is opened here (and e.g. not at the doc or doc_body event)*/ put '[New(1)]' NL ; %DefineColors end ; define event style_class ; put '[Define.Style("SAS.' HTMLCLASS '", 3 ,"'; put FONT_FACE '", ' ; %font_size put ',' ; put '1' / IF CMP ( "bold" , FONT_WEIGHT ) ; put '0' / IF ^ CMP ( "bold" , FONT_WEIGHT ) ; put ',' ; put '1' / IF CMP ( "italic" , FONT_STYLE ) ; put '0' / IF ^ CMP ( "italic" , FONT_STYLE ) ; put ',,,0' ; /* The zero above is there because Excel does not accept a null string, and that would be the result if a non-defined color is used. Apparently a zero is valid. */ %ApplyColor ( FOREGROUND ) ; put ')]' NL ; /* 6: pattern apattern, afore, aback */ put '[Define.Style("SAS.' HTMLCLASS '",6,1,0' ; %ApplyColor ( BACKGROUND ) ; put ',0)]' NL ; end ; define event pagebreak ; start: put '[Workbook.Insert()]' NL ; put '[select("R1C1")]' NL ; end ; define event System_Title ; put '[select("RC2")]' NL ; put '[formula("' VALUE '")]' NL / if exists ( value ) ; put '[select("R[0]")]' NL ; put '[Apply.Style("SAS.' HTMLCLASS '")]' NL / if exists ( HTMLCLASS ) ; put '[select("R[1]C1")]' NL ; end ; define event Proc_Title ; put '[select("RC2")]' NL ; put '[formula("' VALUE '")]' NL / if exists ( value ) ; put '[select("R[0]")]' NL ; put '[Apply.Style("SAS.' HTMLCLASS '")]' NL / if exists ( HTMLCLASS ) ; put '[select("R[1]C1")]' NL ; end ; define event System_Footer ; put '[select("RC2")]' NL ; put '[formula("' VALUE '")]' NL / if exists ( value ) ; put '[select("R[0]")]' NL ; put '[Apply.Style("SAS.' HTMLCLASS '")]' NL / if exists ( HTMLCLASS ) ; put '[select("R[1]C1")]' NL ; end ; define event rowspancolspanfill ; put '[Select("RC[1]")]' NL / if ^exists ( colspan ) ; put '[Select("RC[' colspan ']")]' NL / if exists ( colspan ) ; end ; end; run; /* EXPLANATION of the tagset and macros GENERAL This tagset is defined such that the output of a SAS procedure can be send directly to Excel through DDE. This should work on any Windows platform, as far as I know. It hasn't been tested on all the different Win-flavors, though. USAGE The DDE link is established by defining a FILEREF to the 'system topic' in the following way. FILENAME excelsys dde 'excel|system' ; Of course the choice of the fileref (here 'excelsys') is free. Once the tagset has been defined (as "TagSets.Excel") it can be used by specifying the DDE-fileref on both the BODY and the STYLESHEET option of the ODS statement. ODS tagsets.excel body = excelsys stylesheet = excelsys rs = none ; The "rs=none" option is necessary to get the communication between SAS and Excel to work (don't ask me why). Excel needs the stylesheet info first. I understand that in v9 there will be some changes in the way ODS files are opened and closed. This may make it necessary in v9 to open and close the destination first only for the style-info, to make sure the style info comes first. ODS tagsets.excel stylesheet = excelsys rs=none ; ODS tagsets.excel close ; After that open the destination again to get the output. ODS tagsets.excel body = excelsys rs=none ; You can add other options to the ODS statement, like STYLE=. There are some things to keep in mind concerning the fonts and colors defined in the style. These are discussed in a next paragraph. Note that you can have other ODS destinations open at the same time, "tagsets.excel" is just an ODS destination like any other. THE ODS STYLE I have tried to 'translate' the attributes that can be set in an ODS style to attributes in an Excel style. But the Excel concepts of styles and attributes is rather different from the ODS concept. Important differences are the way things are being defined, the time the information comes available and the time that information is being used. Each style element in the ODS style is translated in an Excel format style. I have concentrated on fonts and on fore- and background colors. FONTS Excel is not able to interpret a list of fonts which are commonly used in ODS styles, indicating that preferably the first font should be used, and if that is not possible the second one, etc. So your ODS style should be defined with a single font like 'docFont' = ("Arial",9pt) instead of 'docFont' = ("Arial, Helvetica, Helv",3); as in the default style. The tagset only can handle fontsizes in integers, in the range from 6 to 14 pt. If you need point size outside this range you can change the %DO loop in the %Font_size macro. If you need non-integers you will have to make more complicated changes to the macro. COLORS The colors in Excel should come from a predefined list of 63 colors, and when a color from that list is redefined all cells that have been assigned that color (either explicitly or by applying a template style with a color defined) change color. When the tagset is executing there is no way to keep track of which or how many colors have been defined. That means that the colors will have to be defined while the tagset is being defined, not while it is executing. In order to make it easy to define an ODS style with colors that are 'known' to the tagset I use some macros from the ColorMac macros in the SASAUTOS library (in the !SASROOT\graph\macro directory), and some adaptations from those macros. These macros also serve to make the switch from the syntax Excel needs for colors and the syntax in which ODS gives the colors to the tagset while executing. Excel wants three decimal numbers in the 0...255 range for 'red', 'green' and 'blue', while ODS delivers values like "#00FF00" (hash and three concatenated hexadecimal numbers, in -luckily- the same range). The macros used in the tagset definition expect a macro variable ColorRGBlist to be filled with RGB 'triplets'. The RGB values in the triplets should be separated by commas, and the triplets themselves should be separated by at least one space. The %csrgb macro (the name is short for 'Comma Separated Red Green Blue') translates SAS colornames into such a triplet. See the example. It is important to make sure that the colors your ODS style uses are made known in this way to Excel. BORDERS I have not tried to translate border and frame information. Instead I have tried to used borders around (groups of) cells to indicate which cells should be considered 'joined' (COLSPAN and ROWSPAN in HTML terms), because Excel doesn't know something like joined cells. */ /* EXAMPLES */ ODS Listing Close ; ODS _ALL_ Close ; FILENAME excelsys dde 'excel|system' ; /* First have ODS write the style info */ ODS tagsets.excel stylesheet = excelsys /* rs=none necessary to write through DDE */ rs=none style = excel ; ODS tagsets.excel close; /* Now write the output for your different PROCs*/ ODS tagsets.excel body = excelsys rs=none ; TITLE 'ODS writes into your Excel sheet' ; PROC UNIVARIATE data = sashelp.class ; RUN ; PROC TABULATE data = sashelp.class ; CLASS sex age ; VAR height weight ; TABLE age = '' * sex = '' , ( height weight ) / box = 'age and sex' ; RUN ; ODS _ALL_ Close ;