Description
dynamic data exchange
2/9/14
DYNAMIC DATA EXCHANGE(DDE) in SAS | SAS KNOWLEDGE
SAS KNOWLEDGE
Clinical SAS Blog BY ANIL KUMAR
DYNAMIC DATA EXCHANGE(DDE) in SAS
Uncategorized March 7, 2013 Leave a comment 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;
anilkumarsas.wordpress.com/2013/03/07/dynamic-data-exchangedde-in-sas/?relatedposts_exclude=150 1/3
2/9/14
DYNAMIC DATA EXCHANGE(DDE) in SAS | SAS KNOWLEDGE
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.
(http://anilkumarsas.files.wordpress.com/2013/03/untitled.jpg) 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:
anilkumarsas.wordpress.com/2013/03/07/dynamic-data-exchangedde-in-sas/?relatedposts_exclude=150 2/3
2/9/14
DYNAMIC DATA EXCHANGE(DDE) in SAS | SAS KNOWLEDGE
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://www2.sas.com/proceedings/sugi31/154-31.pdf)http://analytics.ncsu.edu/sesug/2003/DM08-Li.pdf (http://analytics.ncsu.edu/sesug/2003/DM08Li.pdf) Tagged: clinical sas (http://anilkumarsas.wordpress.com/tag/clinical-sas/), dde in sas (http://anilkumarsas.wordpress.com/tag/dde-in-sas/), dde triplet in sas (http://anilkumarsas.wordpress.com/tag/dde-triplet-in-sas/), dynamic data exchange in sas (http://anilkumarsas.wordpress.com/tag/dynamic-data-exchange-in-sas/), sas (http://anilkumarsas.wordpress.com/tag/sas/), sas interview questions (http://anilkumarsas.wordpress.com/tag/sas-interview-questions/)
Blog at WordPress.com. | The Delicacy Theme. Follow
Follow “SAS KNOWLEDGE”
Powered by WordPress.com
anilkumarsas.wordpress.com/2013/03/07/dynamic-data-exchangedde-in-sas/?relatedposts_exclude=150
3/3
doc_711397569.pdf
dynamic data exchange
2/9/14
DYNAMIC DATA EXCHANGE(DDE) in SAS | SAS KNOWLEDGE
SAS KNOWLEDGE
Clinical SAS Blog BY ANIL KUMAR
DYNAMIC DATA EXCHANGE(DDE) in SAS
Uncategorized March 7, 2013 Leave a comment 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;
anilkumarsas.wordpress.com/2013/03/07/dynamic-data-exchangedde-in-sas/?relatedposts_exclude=150 1/3
2/9/14
DYNAMIC DATA EXCHANGE(DDE) in SAS | SAS KNOWLEDGE
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.
(http://anilkumarsas.files.wordpress.com/2013/03/untitled.jpg) 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:
anilkumarsas.wordpress.com/2013/03/07/dynamic-data-exchangedde-in-sas/?relatedposts_exclude=150 2/3
2/9/14
DYNAMIC DATA EXCHANGE(DDE) in SAS | SAS KNOWLEDGE
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://www2.sas.com/proceedings/sugi31/154-31.pdf)http://analytics.ncsu.edu/sesug/2003/DM08-Li.pdf (http://analytics.ncsu.edu/sesug/2003/DM08Li.pdf) Tagged: clinical sas (http://anilkumarsas.wordpress.com/tag/clinical-sas/), dde in sas (http://anilkumarsas.wordpress.com/tag/dde-in-sas/), dde triplet in sas (http://anilkumarsas.wordpress.com/tag/dde-triplet-in-sas/), dynamic data exchange in sas (http://anilkumarsas.wordpress.com/tag/dynamic-data-exchange-in-sas/), sas (http://anilkumarsas.wordpress.com/tag/sas/), sas interview questions (http://anilkumarsas.wordpress.com/tag/sas-interview-questions/)
Blog at WordPress.com. | The Delicacy Theme. Follow
Follow “SAS KNOWLEDGE”
Powered by WordPress.com
anilkumarsas.wordpress.com/2013/03/07/dynamic-data-exchangedde-in-sas/?relatedposts_exclude=150
3/3
doc_711397569.pdf