. . . . . . . . . . . . . . . . . . .
Dynamic Data Exchange: “Working Smarter - Not
Harder” (SAS)
Rachel McPherson
October 2012
Outline
• What is dynamic data exchange
• Uses & How it was applied in current work environment
• Pros & Cons
• Example of DDE
Dynamic Data Exchange
• Dynamic Data Exchange (DDE) is a method of dynamically exchanging information between Windows applications.
SAS
Excel
Word
Uses
• Provides a consistent presentation across output destinations
• Can manipulate formats from other applications as a server client.
DDE - Pros
• Timeliness: Run SAS code to update an annual excel template
• Reduces the chance for error
• Reduces the amount of formatting required
• Can be used to place data in MS Word/Excel
DDE - Cons
• Not good for frequency listings that may change. For example: causes of death.
• If a formatting change is made to the excel/word file, your program must be changed.
Year 1 Year 2
Cancer Cancer
Respriatory Disease Respriatory Disease
Injury Cirulatory Disease
Injury
SAS code: Excel
• Step 1: • Open Excel file
• Suspend SAS for 1 seconds to allow Excel to be fully started
options noxwait noxsync;
%SYSEXEC "W:\hltdss\Home\Projects\DSS\H1N1\SAS\H1N1\Reports\MOH Report Wave 2.xls";
data _null_;
x=sleep(1);
run;
SAS code: Excel
Step 2:
Tell SAS the range of cells where the data is going to be stored: Row 22 Column 4 to Row 22 Column 5 (r22:c4:r22c5)
• %macro MOH;
• filename opendiag dde "excel|W:\hltdss\Home\Projects\DSS\H1N1\SAS\H1N1\Reports\[MOH Report Wave 2.xls]Summary!r22c4:r22c5";
• data _null_;
• file opendiag;
• set step45 (drop=week3);
• put (_all_) ($);
•
• run;
• %mend;
• %MOH;
SAS code: Word
• Step 1: • Open the Word file
• Suspend SAS for 1 seconds to allow Word to be fully started
options noxwait noxsync;
%SYSEXEC "W:\hltdss\Home\Projects\DSS\CCHS Report\Draft\CCHS Report mockup - Burntwood.docx";
data _null_;
x=sleep(1);
run;
SAS code: Word
Step 2:
In word you must have a template and then bookmark the sections you would like SAS to change
For example:
Open your word document
Go to the “Insert “ Tab
Highlight the area you want bookmarked
Press Bookmark
Name your Bookmark
Press Add
SAS code: Word
SAS code: Word
filename opendiag dde 'winword|W:\hltdss\Home\Projects\DSS\CCHS Report\Draft\CCHS Report mockup - Burntwood.docx!bookmark1'; /*Makes changes to Bookmark1*/
data step1;
dk='0.9% '; /*This is what I want in Bookmark1*/
run;
data step2; /*Places the data into Bookmark1*/
set step1;
file opendiag;
put (_all_) ($);
run;
SAS code: Word
Step 3:
Formatting your text:
filename opendiag dde 'winword|system';
data _null_;
file opendiag;
put '[EditGoto.Destination=
"bookmark1"]';
put '[FormatFont.Font="Calibri",
.Points="9",.Bold=0]';
run;
References
• http://www2.sas.com/proceedings/sugi31/154-31.pdf
• http://www2.sas.com/proceedings/sugi28/016-28.pdf
• http://www2.sas.com/proceedings/sugi28/145-28.pdf
• http://mwsug.org/proceedings/2010/excel_db/MWSUG-2010-166.pdf
• http://sas.connectsolutions.com/p76172942/
Questions