DYNAMIC DATA EXCHANGE(DDE) in SAS

A QUICK STARTER

Objective: This post discusses a generally unknown and underutilized utility in SAS which gives programmers a lot of flexibility and power if utilized properly.

Theory: 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.

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.

http://www2.sas.com/proceedings/sugi31/154-31.pdf

http://analytics.ncsu.edu/sesug/2003/DM08-Li.pdf

Conclusion : After reading this post the users will be having a good workable knowledge on the data exchange techniques outlined above and I will expect you to start utilizing its capabilities.

Will be back with some more magic of SAS. Till then Goodbye.

References:

The little SAS Book 4^th edition.

Saurabh Singh  Chauhan

(er.chauhansaurabh@gmail.com)         

Note: Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.