|-----------------------------------------------| |UCNS Computer Review | |University Computing and Networking Services | |University of Georgia | |Computer Services Annex | |Athens, Georgia 30602 | |E-Mail: BITNET helpdesk@uga | | Internet helpdesk@uga.cc.uga.edu | |_______________________________________________|
Newsletter: Winter 1994
Author: Gregory S. Nelson
This article is the first of a two part series which will explore two aspects of using SAS 6.08 for Windows: transferring SAS data and SAS graphics to Windows-based software applications. (Users will find that much of this is also valid for SAS 6.08 for OS/2.) The goal of Part I is to explain Dynamic Data Exchange (DDE) and how you can use it to both read and write data from SAS with other software packages. The goal of Part II will be to explain how to take some SAS/Graph output and convert it into something you can use with WordPerfect and other Windowsbased software applications.
In this article we will show you how to do the following:
Unfortunately the analogy breaks down when we talk about a client/server relationship, because not all applications can send and receive information from other applications. Applications that support DDE may do so to different extents. For example, Microsoft Excel Versions 3.0 and 4.0 and Lotus 1-2-3/G or higher both fully support DDE server mode. The SAS System can read data and send data or system commands to these packages. However, with packages such as Microsoft Access (MSACCESS) for Windows and Borland's Paradox Version 1.0, you can read data but not send data directly to SAS via DDE.
In order for the SAS System to send and receive data from a DDE compliant application, both applications must be currently executing and the desired file(s) must be open in the server application. In SAS the FILENAME statement (with the DDE keyword) points to the DDE application you want to interact with.
Perhaps the best way to describe these concepts is through some concrete examples. The example I will use throughout this article ties in some commonly requested features from the data manipulation side as well as the graphics capabilities found in SAS 6.08 for Windows.
Our data set was taken from "Observations: The Technical Journal for SAS Software Users" (Vol. 2, No. 1, Fourth Qtr, 1992). The goal of the example is to read a raw data file (a Lotus 1-2-3 spreadsheet) which has two variables, DATE and TEMP, which represent temperatures taken over five days. The task is to plot the high, low, and mean temperatures using SAS Graph. In addition, we'll transfer the final data set, including the summary statistics produced in SAS, back into an Excel 4.0 for Windows worksheet.
Accessing the Data from a Lotus Spreadsheet Many people enter their data on spreadsheets and then transfer it to a statistical package such as SAS, finding it a convenient way to arrange their data. Spreadsheets are usually easy to use and provide a good way to calculate simple summaries and allow for production of graphics. With Release 6.08 of SAS for Windows or OS/2, we can now take that data directly from an open spreadsheet and import it directly to a SAS data set without first exporting the data or writing complicated code to achieve the data transfer.
Figure 1 shows our sample data set and how it might look in a Lotus 1-2-3 spreadsheet.
Figure 1
|---------------------------------------------|
| My data: Temperatures collected for |
| 5 days, twice a day. |
| |
| Date Temperature |
| 01-Apr-92 45 |
| 01-Apr-92 73 |
| 02-Apr-92 49 |
| 02-Apr-92 75 |
| 03-Apr-92 52 |
| 03-Apr-92 60 |
| 04-Apr-92 39 |
| 04-Apr-92 62 |
| 05-Apr-92 50 |
| 05-Apr-92 72 |
|---------------------------------------------|
Figure 1
We have entered the data along with useful titles and column
headings, and we will find out that we do not have to change
anything or save it as something else to transfer the data
into SAS. The first step is to make sure that SAS 6.08 for
Windows is currently running and that
Lotus 1-2-3 is running and your spreadsheet is open.
Next, in order to transfer data via DDE, we must determine the DDE triplet. The easiest way to do this is to mark the range of data in the spreadsheet you want to transfer via DDE. Then choose Copy from the Edit menu item in Lotus to store the marked data in the clipboard. While Lotus is running, move to your SAS session and select Options - DDE Triplet from the menu bar. The DDE triplet will be displayed in an information box. The triplet will already be marked in the information box. You can press CONTROL-INSERT to copy the triplet into the internal SAS clipboard. Finally, paste the triplet into the FILENAME statement. You'll see
FILENAME DAILY DDE " ";
While in INSERT mode, position the cursor between the double quotes and choose Paste from the Edit menu to complete the FILENAME statement with the DDE triplet.
This may seem somewhat confusing, but SAS needs to know "who" it is talking to and what ranges of cells to include. The first time you do this will probably be the most cumbersome. Soon, you will know what the DDE triplet is and what the ranges are so you can just type them into the filename statement.
Once the FILENAME is constructed, you will use the fileref in the INFILE statement to read data and the FILE statements to send data. Figure 2 shows the program and output to indicate what a sample session might look like. (We are using SAS for OS/2 in this screen to read data from Lotus for Windows in a WIN-OS2 session.)
Figure 2
|===========================================================|
| 00005 FILENAME daily dde '123W|C:\TEMP.WK4!A:B6..A:C15'; |
| 00006 DATA DAILYTEM; |
| 00007 INFILE DAILY; |
| 00008 INPUT DATE date9. TEMP; |
| 00009 FORMAT DATE DATE9. TEMP; |
| 00010 RUN; |
| 00011 PROC PRINT; |
| 00012 RUN; |
| 00013 |
|===========================================================|
| |
|LOG OUTPUT |
|File Edit View Globals Help | File Edit View Globals Help |
| | |
|NOTE: The infile DAILY is | The SAS System |
| FILENAME=123W|C:\temp | |
| RECFM=V, LRECL=132 | OBS DATE TEMP |
| | |
|NOTE: 10 records were read | 1 01APR1992 45 |
| The minimum record | 2 01APR1992 73 |
| The maximum record | 3 02APR1992 49 |
|NOTE: The data set WORK.DA | 4 02APR1992 75 |
|NOTE: The DATA statement | 5 03APR1992 52 |
| | 6 03APR1992 60 |
| | 7 04APR1992 39 |
|11 PROC PRINT; | 8 04APR1992 62 |
|12 RUN; | 9 05APR1992 50 |
| | 10 05APR1992 72 |
|NOTE: The PROCEDURE PRINT | |
|===========================================================|
Figure 2
Creating a SAS data set (summarized data)
Once the data has been transferred into SAS via DDE, we are ready to analyze our data. A common task for many SAS users is to summarize the data and save those means, to plot them or put them into a table for presentation.
The SAS code (shown in Figure 3) takes the data set we just created and produces means, then outputs those means into a second SAS data set.
Figure 3
|-------------------------------------------------|
| /* Calculate the mean temperature */ |
| Proc Means data=dailytem mean noprint; |
| var temp; |
| by date; |
| output out=meands(keep=date avgtemp) |
| mean=avgtemp; |
| run; |
---------------------------------------------------
Figure 3
We then create a new data set with the original values and merge them with the means.
/* Create a new data set with high, low and mean values */
Data both;
merge dailytem meands;
by date;
run;
Finally, we print them.
/* Print the resulting data set */
Proc Print data=both;
run;
Now that we have two data sets (dailytem and meands), we want to send them back to a spreadsheet so we can have a nice table for a presentation.
Transferring the summarized data to Excel 4.0 for Windows SAS can serve as both a client and a server for DDE compliant applications. This means that SAS can receive as well as transmit data to other Windows applications. In our example, we want to take some of the summary data and put it back into a spreadsheet. This time, we are going to transfer the data into a range of cells in Microsoft Excel 4.0 for Windows.
Just as we had to make sure that Lotus and SAS were currently executing, we need to make sure that SAS and Excel are both running. When you first start Excel 4.0 for Windows, a new worksheet called "Sheet1" is automatically opened. For this example, we will use Sheet1 to write out our data.
As in the example we showed earlier which described reading data from Lotus, we need to find the DDE triplet for the application we want SAS to write. You can follow the steps described above to find that information. Here, for brevity's sake, we know the DDE triplet is going to be:
'EXCEL | SHEET1 ! R1C1:R10C3'
The SAS code to write out the data set we created above is as follows:
FILENAME outtemp DDE 'EXCEL | SHEET1 ! R1C1:R10C3'
DATA datetemp;
SET both;
FILE outtemp;
PUT date temp avgtemp;
RUN;
This program will take the five days of data and the three variables (DATE, TEMP, and AVGTEMP) and plug them into the first ten rows and first three columns.
DDE is an example of client/server technology where information can be shared among application software running on the same machine. It is a very powerful tool for sharing data and can save SAS users time and hassle from converting data into other forms.
To find more information about how you might take advantage of some of the other features that SAS for Windows and OS/2 can offer, please contact the UCNS Statistical Software
Support Group.
542-3106
statsoft@uga.cc.uga.edu
In the next issue of this publication, we will discuss using the graphics capabilities found in SAS with Windows applications such as WordPerfect.