DYNAMIC DATA EXCHANGE(DDE) in SAS

DDE: Dynamic data exchange alias DDE is a way for data transfer and exchange between Microsoft windows applications. SAS can utilize this functionality to transfer data to applications like MSexcel or MSword in a flexible and dynamic way.
DDE ADVANTAGES:
 
I know I know that right now you are saying that why in the world should I learn this new concept when I have PROC IMPORT and PROC EXPORT available, I even can use ODBC or just can import data using SAS EG.
Here is the answer…
When you are talking about proc import and proc export, these procedures are simple to use but provide less functionality to modify you data as DDE uses data step directly to transfer data. Secondly you should have an extra license SAS/ACCESS to use these procedures but DDE is just BASE SAS(windows).
ODBC also has same problem, an extra license.
SAS EG is a complete new SAS Client you have to buy and again the free thing is more flexible than this costly software so why not go for free, after all we love free stuff..!! J
DDE DISADVANTAGES:
 
Not keeping the debate one sided there are some disadvantages with DDE as well:
1) It works only in windows environment.
2) Application must be running on same computer from which SAS is exchanging data. Though we have a workaround for this, where we will start the application using SAS.
READING DATA USING SAS:
Here I will be discussing 3 ways to read data in SAS from MS Excel:
1) Getting data from clipboard:
This method is very simple, open Excel data, copy data using our favorite Ctrl-C which will send it to clipboard and then we can use SAS LIBNAME statement and DATA STEP to get that data.
First open an Excel file select the data you want to input as SAS dataset and press CTRL-C.
Then in SAS window write the following code:
filename bmi DDE ‘CLIPBOARD’;
data bmi;
  infile bmi missover;
  input ID SEX AGE EDUC CIG WEIGHT;
run;
So the dataset bmi is created with the observations copied to the clipboard. Pretty simple isn’t it..!!
Also now as you have the data step working with that data so run your imaginations wild and you can mould the data in any way before outputting it to the dataset.
2) Second technique is similar but it has a slight advantage over the previous technique that it does not require to copy data, but an extra step is also required, you need to find the DDE triplet which is of the form
Application|topic!item
But problem is how you will find the DDE triplet. It can be found out by copying the data from corresponding application then going into the Solutions menu -> Accessories -> DDE Triplet, this will give you the triplet.
Untitled
Now you will say this has not one step less but 2 steps extra. we had to copy data anyway and then go to solution menu and copy the triplet.
But this can be done once in a reusable code. And the triplet is self intuitive and you don’t need to copy it necessarily, you can type it yourself, I was just telling a way to get it with zero possibility of errors.
Now using the triplet you can write the following code to get your data:
filename bmi DDE ‘Excel|F:\SAS\sas datasets\[bmi1.xls]bmi1!R2C1:R10C6’;
data bmi;
  infile bmi missover;
  input ID SEX AGE EDUC CIG WEIGHT;
run;
Same as previous code just CLIPBOARD is replaced with the triplet.You can just change this triplet according to your needs.
NOTE: The application(Excel in this case) should be running when you are running this code.
3) Thirdly, we can just start the application in SAS itself so we are now free from the above requirement of application to be running.
The code goes something like:
OPTIONS NOXSYNC NOXWAIT;
X ‘”F:\SAS\sasdatasets\bmi1.xls”‘;
FILENAME bmi DDE ‘Excel|F:\SAS\sasdatasets\[bmi1.xls]bmi1!R2C1:R10C6’;
DATA sales;
  infile bmi missover;
  input ID SEX AGE EDUC CIG WEIGHT;
RUN;
The options NOXWAIT and NOXSYNC are mandatory and they tell SAS to not keep on waiting for user input after opening the application and return control.
The X tells SAS that what follows in single quotes is a windows command and just specifying the file path and name tells SAS to open it.
Rest is all same. You specify the DDE triplet and file is read into SAS dataset.
SO I have explained the 3 ways and all three are pretty simple and flexible so use them generously.
Further reading:
 
Below are a few interesting reads from the topic which will enhance your knowledge further.

 

Advertisements

Tagged: , , , , ,

One thought on “DYNAMIC DATA EXCHANGE(DDE) in SAS

  1. […] DYNAMIC DATA EXCHANGE(DDE) in SAS. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: