DATA TRANSFER TO AND FROM EXCEL WITH DYNAMIC DATA EXCHANGE (DDE) DDE (Dynamic Data Exchange) allows one to import or export data between PC applications. In this document I'll focus primarily on how it works between SAS and Excel and briefly describe data exchange between SAS and WORD. DDE will not only allow you to read data from an Excel spreadsheet into a SAS dataset but also will place information from in a SAS dataset into an Excel spreadsheet with more control over the placement of data and formats than available with PROC EXPORT. You can also write Excel commands to specific cells in a specified worksheet from the DATA step. Please note that Excel must be running and the existing workbook open from which data are to be read or to which data are to be written. This is the opposite of the requirement for importing data with PROC IMPORT (which assumes the Excel workbook is closed). * How to open an unnamed excel workbook with an empty worksheet; options noxwait noxsync; x '"c:\program files\microsoft office\office10\excel"'; data _null_; x=sleep(2); run; filename cmd dde 'excel|system' ; Read Data from Microsoft Excel with DDE This section demonstrates how SAS can read data from an Excel workbook into a SAS dataset through DDE. It is more flexible than PROC IMPORT in that it allows you more flexibility to specify the desired range from a worksheet. It also allows you to write commands and formulas and enter them into specific cells of a worksheet. In the following FILENAME statement, the DDE link is first established using Microsoft Excel to a workbook called pt.xls that contains a worksheet named zr in the lower left-hand corner. The statement also specifices that data are to be read from rows 1 through 22 and columns 1 through 10 (note that variable names, as described earlier, are assumed to be placed in the first row of the specified range). FILENAME dat DDE 'Excel|C:\regress\zeros\[pt.xls]zr!R1C1:R22C10'; DATA zr; INFILE dat missover; INPUT id $ z1 z2 z3 z4 z5 z6 z7 z8 yPT; RUN; PROC PRINT DATA=zr; run; The string after DDE in the FILENAME statement is called the DDE triplet which consists of: application | topic ! item. The specific form of the DDE triplet differs across applications. Fortunately, there is a simple way to determine the contents of the DDE triplet without typing them. First, open the Excel spreadsheet you want to read, highlight the rows and columns to transport to SAS and copy them to the clipboard. Then go into the PC SAS menus and choose Solutions – Accessories - DDE Triplet The dialog box that appears contains the DDE triplet. Copy it from the dialog and add it between the quotes on the filename statement (right-click on the shaded area with the mouse and then click Copy with the left side of the mouse). The triplet actually tells SAS to read data from the spreadsheet beginning in row 2 since the first row is assumed to contain the variable names. The MISSOVER option on the INFILE statement prevents SAS from going to a new input line if it does not find values in the current line for all the INPUT statement variables. The MISSOVER option specifies that when the INPUT statement reaches the end of the current record values that are expected but not found on that record are set to missing. Reading Missing Data Missing data or blank cells can pose a problem when they are read from an Excel spreadsheet with DDE. In this example, data will be read from columns 1 through 3 and rows 2 through 15. Some of the data cells may be blank. Also, some of the character data have blanks. Both situations may require special handling when reading with DDE. The first few rows of the example data look like: row name town team 2 John Raleigh Cardinals 3 Jose North Bend Orioles 4 Kurt Yelm Red Sox 5 Brent Dodgers ... The SAS code that will read these data correctly into a dataset. Note the INFILE statement is structured exactly as if one were reading a text file. FILENAME mydata DDE 'excel|sheet1!r2c1:r15c3'; DATA in; INFILE mydata NOtab dlm='09'x dsd missover; INFORMAT name $10. town $char20. team $char20.; INPUT name town team; RUN; PROC PRINT DATA=in; run; The INFILE statement includes the NOtab option which tells SAS to not convert tabs sent from the Excel worksheet into blanks. Therefore, the tab character can be used as the delimiter between data values. The DLM= option specifies the delimiter character: '09'x is the hexadecimal representation of the tab character. The dsd option specifies that two consecutive delimiters represent a missing value. The default delimiter is a comma. The MISSOVER option prevents SAS from going to a new input line if it does not find values in the current line for all the INPUT statement variables. With the MISSOVER option, when an INPUT statement reaches the end of the current record, values that are expected but not found are set to missing. The INFORMAT statement forces the DATA step to use modified list input, which is crucial to this example. If you do not use modified list input, you receive incorrect results. The necessity of using modified list input is not DDE specific. You would need it even if you were using data in a CARDS statement, whether your data were space or comma delimited. Data Transfer from SAS to Excel with DDE Dynamic Data Exchange (DDE) allows full programmatic control over exactly where data are to be inserted in the Excel worksheet. Values from SAS datasets may be placed into either a blank Excel workbook or into an existing one. DDE allows you to specify the name of the workbook, the worksheet that is to receive the data, and the data range through the upper-left to lower-right designation of the data-block. The worksheet cells can even be formatted with font, font-size, and column width. You can also apply Excel commands from a DATA step. The following examples show only a few of the features this method of data transfer offers. In order to submit these commands, the Excel must be running, the respective workbook opened, and the designated worksheet assigned the given name in the tab found in the lower left corner. Worksheets are given default names of Sheet1 – Sheetn (depending on the number of worksheets available). You need to enter these particular worksheet names if you haven’t assigned your own. In this first example, the DDE link is established to Microsoft Excel by sending data to SHEET 1 of a yet unnamed workbook with sheet1 opened (see next example for an existing workbook and worksheet). Variable names are placed in the first row and the data are placed into rows 2-21 and columns B, C, and D (i.e., c2,c3,c4). FILENAME data DDE "EXCEL|sheet1!r1c2:r26c4"; DATA _null_; FILE data; PUT 'x' '09'x 'y' '09'x 'z'; *place variable names in row 1; DO i=1 to 20; x=3*ranuni(i)-2; y=x+10; z=x/2; PUT x y z; END; RUN; If two or more EXCEL workbooks are open simultaneously, or if you want to write data to an open workbook with a specific worksheet name, the drive and path are entered along with the name of the workbook placed in square brackets (be sure it has an .xls extenstion) next to the worksheet name followed by an ! as shown in the next example. * 'Excel|drive:[.xls]!range’; FILENAME rndm DDE 'Excel|C:\sas\excel\[brand.xls]sales!R2C1:R10C5'; DATA _null_; Brand=1; month=’Jan’; year=1999; product=’Shoes’; FILE rndm; PUT ‘Brand’ '09'x ‘month’ '09'x ‘year’ '09'x ‘product’; PUT Brand month year product; RUN; The NOTAB Option SAS automatically places a tab character between adjacent variables when data are transmitted across the DDE link. When the NOtab option is entered at the end of the FILENAME statement that specifies the DDE keyword, SAS accepts character delimiters other than tabs between variables. The NOtab option also can be entered to store character strings which include embedded blanks, into one spreadsheet cell. For example, when a link is established between SAS and the Excel application each word of a character string is normally stored in a single cell. To store the entire string, including embedded blanks in a single cell, enter the NOtab option as shown in the following example: /* Without the NOTAB option, column 2 contains 'test' and column 3 contains 'one' column 4 contains ‘test’ and column 5 has ‘two’ */ FILENAME rndm DDE 'Excel|C:\sas\excel\[brand.xls]brnd!R1C2:R2C5'; DATA _null_; file rndm; a='test one'; b='test two'; put a $9. b $9.; run; /* You can enter the NOTAB option to store each variable in a separate cell. To do this, you must force a tab ('09'x) between each variable, as in the PUT statement. After running this DATA step, Column 2 contains 'test one' and column 3 contains 'test two'. */ FILENAME rndm DDE 'Excel|C:\sas\excel\[brand.xls]brnd!R1C2:R2C5' NOtab; data _null_; file rndm; a='test one'; b='test two'; put a $9. "09"x b $9.; run; /* Microsoft defines the DDE topic SYSTEM to enable commands to be invoked within Excel. */ Suppose you want to write a formula to a specific Excel cell with DDE in SAS. For testing, write the formula 1+1 in cell A1, so that Excel recognizes it as a formula and displays "2". Excel formulas start with an equals sign (=). OPTIONS NOxwait xsync; FILENAME cmds DDE 'Excel|system'; %let openfile = '[open("C:\sas\excel\test.xls")]'; DATA _null_; FILE cmds; PUT &openfile; RUN ; FILENAME ddedata DDE 'Excel|Sheet1!R1C1:R1C1'; data _null_; file ddedata notab; put '=1+1'; run; data _null_; file cmds; put '[quit()]'; run ; ************************ ; FILENAME cmds dde "excel|system"; /* SAS PUT statements in a DATA step execute Excel macro commands */ data _null_; file cmds; put '[SELECT("R1C1:R25C2")]'; put '[SORT(1,"R1C1",1)]'; put '[SAVE()]'; put '[QUIT()]'; run; DDE allows you to specify the name of the workbook and worksheet to receive the data. In this example the upper-left cell of the data-block was chosen.