Using_DDE_to_connect_SAS_with_Excel
Using DDE to connect SAS? results with MS-Excel?
Rebecca Symes, Maine Health Information Center, Manchester, Maine
INTRODUCTION
SAS? is clearly superior software for gathering and processing large amounts of data. However, most clients have skills in other areas and no inclination to learn SAS. They want output in a format they consider to be more user-friendly, and the tool of choice for many is Microsoft Excel?.
PROC EXPORT will output SAS data to an Excel file, and ods is the new sophisticated cousin. However, the flexibility of a DDE link in a standard DATA step allows SAS results to be placed precisely into a specific location in the Excel file. Formulas, formatting, even graphs are untouched, yet the data can be updated in seconds. In addition to being useful, it is also loads of fun to watch – as close to magic as one can get inside a cubicle!
DDE
DDE stands for Dynamic Data Exchange, and is a standard utility for translating from one Windows application to another. DDE is often used for reading in data from an application, such as Excel, for further processing within the SAS program.
The DDE link presented in this paper is going out from SAS to Excel. The DDE link is built as part of a FILENAME statement which includes the specific row and column location for output. Being a simple utility, DDE output must sometimes be managed within SAS to ensure maximum value.
THE BASICS
The core of DDE output is in a FILENAME statement and a FILE, PUT within a DATA step. The FILENAME defines the output location and PUT selects data fields to be output.
filename outxls dde ‘EXCEL|loading!r6c1:r8c3’;
where outxls is the nickname
dde instructs the link to use DDE to exchange data
EXCEL is the Windows application object
loading is the page name in Excel (the default name would be Sheet n, but I have found renaming it for clarity to be a useful step)
r6c1:r8c3 – Exact row and column numbers where the data will be loaded.
The complete command set would look like the following:
Figure 1.
Figure 2.
In Excel, the green section in Figure 2. shows where the filename statement will put raw data. Row and
column locations can be larger than needed, in case you’re not sure how many rows will be exported.If fewer rows and colums than needed are defined in the Filename statement, the extra data will not be
output. For example, if you have eight columns of data and the output is set up as r1c1:r6c7, the eighth
field will not be output to Excel.
Defining your output area with more rows or columns than needed is fine, as long as there isn’t
something else in that spot in your Excel loading area. Looking at Figure 1., if the row/column
instruction were set as r6:c1:r20c3, everything in the Excel file from cell A9 through C20 would be overwritten with blanks. This is another reason to have your graphs and formatted print areas on a separated tab from where the data is being loaded.
OPENING THE EXCEL FILE
Over many years of using this DDE link, I have found it easiest to open the correct Excel file before running the SAS File and Put code. However, it is possible to have SAS open the Excel file for you, for a completely automated process. The X command will activate the process for Windows to open Excel.
Figure 3.
Figure 3. shows the series used to start Excel, close the default workbook, and open the workbook where SAS will be loading data in a later step. The path may need to be modified for the user’s system. The sleep command allows a pause while Excel.exe is being executed.
THE QUIRKS
BUT WHAT IF….LABELS PARSE OUT ACROSS COLUMNS IN EXCEL?
File and Put work well with numeric data and text without imbedded blanks. However, the purpose of DDE is to exchange data from a dataset into precise columns in Excel. If Excel meets a space, it assumes “new column.”
To prevent text from parsing, just add the NOTAB option to the DDE filename statement.
filename bigout dde ‘EXCEL|loading!r5c1:r50c1’ notab;
Each field using the NOTAB option needs it’s own FILENAME statement, otherwise it will put all your data into a single field. Use a mix of NOTAB and regular FILENAME statements to place data exactly where you need it.
Figure 4.
Note that the filename statement with NOTAB is only a single column wide. The second filename statement starts in the second column and places the rest of the data. You can use as many FILE and PUT statements as needed to output the data.
Figure 5.
BUT WHAT IF….YOUR ROW COUNT IS DIFFERENT NEXT TIME YOU OUTPUT YOUR DATA?
This is where DDE and SAS join forces to make your life easier. One of the biggest advantages to Excel is that it will perform the same calculations or graphics analysis over and over on new data supplied to a standard “loading” area. One of the biggest disadvantages of Excel is that it expects the data to be in exactly the same place every time.
For example, you are creating a report by area and group. Most areas have 5 groups, but some only have 3 or 4. To make Excel happy, SAS needs to modify your dataset so that every area looks like every other area, and all of them have 5 groups, even though some have dummy data. One way to do this is to create a dataset with all the correct rows, and force your data to match.
RESULTS
After running the SAS code with your DDE link, File and Put commands, data is placed in Excel in the rows and columns where Excel can use it for standard displays. Figures 7. and 8. show the automated results which can be repeated many times with no reformatting. Again, usually the loading and graphics areas would be separate tabs, but for clarity they are presented together here.
Figure 7.
Figure 8.
IN EXCEL
The purpose of the SAS/Excel link is place intelligent SAS results into Excel for graphs, beautification, and sending to clients who always want to “play” with the data. Part of the magic is on the Excel side of the connection. There are a few basic hints which make the handoff easier.
1. Place your SAS results into a separate tab in the Excel workbook. I call mine “Loading” just to
remind me why it’s there. Row and column headers, shading, borders, and especially graphs, can be applied by using a formula to link from another Sheet to the Loading tab. Data can be continually refreshed without disturbing the pretty part.
2. When sending formatted chart data to a client, use Copy> Save As> Values to remove the formulas
linking to the real data, then delete the Loading tab and save as a different filename. No point in giving away your secrets. Graphs, however, need to keep their source data in the file.
3. Even when using this automated link, Excel sometimes has a hard time holding it’s formatting when
graphing new data. Check graphs for fonts and sizing before sending out.
THE BOTTOM LINE
Sometimes the most simple tools are the most effective. Excel spreadsheets are easy to maintain and share. SAS is the superior tool to process data and summarize results. Using the simple DDE link allows SAS power to populate a user-friendly spreadsheet, and update the results as needed. With larger SAS processes, there is sufficient time between starting the SAS run and data being loaded into the Excel file. That time break allows the user to switch windows to Excel, and watch the data “magically” appear as each DDE link is executed. The DDE link is a simple tool, easy to execute, creates great results, and is fun to watch.
REFERENCES
SAS Help and Documentation, DDE (Dynamic Data Exchange), writing to Excel. ACKNOWLEDGMENTS
SAS is a Registered Trademark of the SAS Institute, Inc. of Cary, North Carolina
Microsoft Excel is a Registered Trademark of the Microsoft Corporation.
CONTACT INFORMATION
Rebecca Symes
Maine Health Information Center
16 Association Drive
P O Box 360
Manchester, Maine 04351
Work Phone: (207) 623-2555 x-134
Fax: (207) 622-7086
Email: bsymes@https://www.wendangku.net/doc/6f16646698.html,