options noxwait noxsync; x '"C:\program files\microsoft office\office14\excel.exe"';
/* The DDE link is established using */ /* Microsoft Excel SHEET1, rows 1 */ /* through 100 and columns 1 through 3 */ filename random dde 'excel|sheet1!r1c1:r100c3'; data random; file random; do i=1 to 100; x=ranuni(i); y=10+x; z=x-10; put x y z; end; run;
filename testit dde 'winword|"c:\temp\testing.doc" !MARK' notab; data _null_; file testit; put 'This is a test.'; run;
/* The DDE link is established using */ /* Microsoft Excel SHEET1, rows 1 */ /* through 10 and columns 1 through 3 */ filename monthly dde 'excel|sheet1!r1c1:r10c3'; data monthly; infile monthly; input var1 var2 var3; run; proc print; run;
filename testit dde 'winword|"c:\temp\testing.doc" !MARK' notab; libname workdir 'c:\temp'; /* Get ready to read the first bookmark. */ data workdir.worddata; length wordnum $5; infile testit; input wordnum $; run; proc print; run;
/* This code assumes that Excel */ /* is installed on the current */ /* drive in a directory called EXCEL. */ options noxwait noxsync; x '”c:\program files\microsoft office\office14\excel.exe”'; /* Sleep for 60 seconds to give */ /* Excel time to start up. */ data _null_; x=sleep(60); run; /* The DDE link is established using */ /* Microsoft Excel SHEET1, rows 1 */ /* through 20 and columns 1 through 3 */ filename data dde 'excel|sheet1!r1c1:r20c3'; data one; file data; do i=1 to 20; x=ranuni(i); y=x+10; z=x/2; put x y z; end; run; /* Microsoft defines the DDE topic */ /* SYSTEM to enable commands to be */ /* invoked within Excel. */ filename cmds dde 'excel|system'; /* These PUT statements are */ /* executing Excel macro commands */ data _null_; file cmds; put '[SELECT("R1C1:R20C3")]'; put '[SORT(1,"R1C1",1)]'; put '[SAVE()]'; put '[QUIT()]'; run;
/* Without the NOTAB option, column1 */ /* contains 'test' and column2 */ /* contains 'one'. */ filename test dde 'excel|sheet1!r1c1:r1c2'; data string; file test; a='test one'; b='test two'; put a $15. b $15.; run; /* You can use the NOTAB option to store */ /* each variable in a separate cell. To */ /* do this, you must force a tab */ /* ('09'x) between each variable, as in */ /* the PUT statement. */ /* After performing this DATA step, column1*/ /* contains 'test one' and column2 */ /* contains 'test two'. */ filename test dde 'excel|sheet1!r2c1:r2c2' notab; data string; file test; a='test one'; b='test two'; put a $15. '09'x b $15.; run;
/* Enter data into Excel SHEET1 in */ /* row 1 column 1. When you */ /* are through entering data, place */ /* any character in row 5 */ /* column 1, and the DDE link is */ /* terminated. */ filename daily dde 'excel|sheet1!r1c1' hotlink; filename status dde 'excel|sheet1!r5c1' hotlink; data daily; infile status length=flag; input @; if flag ne 0 then stop; infile daily length=b; input @; /* If data have changed, then the */ /* incoming record length */ /* is not equal to 0. */ if b ne 0 then do; input total $; put total=; output; end; run;
'!DDE_FLUSH'
issued
in a PUT statement instructs SAS to dump the contents of the DDE buffer.
This function allows you considerable flexibility in how DDE is used,
including the capacity to transfer data dynamically through the DATA
step. The following example creates a Macro Sheet in Microsoft Excel.
Commands are then written to the Macro Sheet, which is renamed Sheet1
to NewSheet. After writing these commands, through the use of !DDE_FLUSH
, the Excel Macro can be executed in the same DATA Step as it is written.
filename cmds dde 'excel|system'; data _null_; file cmds; /* Insert an Excel Macro Sheet */ put '[workbook.insert(3)]'; run; /* Direct the Output to the Newly created Macro Sheet */ filename xlmacro dde 'excel|macro1!r1c1:r5c1' notab; data _null_; file xlmacro; put '=workbook.name("sheet1","NewSheet")'; put '=halt(true)'; /* Dump the contents of the buffer, allowing us to both write and */ /* execute the macro in the same DATA Step */ put '!dde_flush'; file cmds; /* Run Macro1 */ put '[run("macro1!r1c1")]'; put '[error(false)]'; /* delete the Macro Sheet */ put '[workbook.delete("macro1")]'; run;
C:\test.xls
.
Since macro triggers such as ampersands and percents are treated
as text within single quotation marks, a Macro quoting function must
be used. %STR is used to mask each individual apostrophe separately.
Anytime you have an unmatched apostrophe or parenthesis then it
must be preceded by a percent sign and since each apostrophe needs
to be treated independently of each other the percents are needed.
Once %STR has hidden the apostrophe, the macro variable &excelOne
resolves. %UNQUOTE is then used to remove what %STR has done and
restores each apostrophe around the resolved value leaving the result
as: '[open("C:\test.xls")]'
options mprint symbolgen; filename cmds dde 'excel|system'; %let excelOne=C:\test.xls; data _null_; file cmds; put %unquote(%str(%'[open("&excelOne")]%')); run;
... 10 John Raleigh Cardinals 11 Jose North Bend Orioles 12 Kurt Yelm Red Sox 13 Brent Dodgers ...
filename mydata dde 'excel|sheet1!r10c1:r20c3'; data in; infile mydata dlm='09'x notab dsd missover; informat name $10. town $char20. team $char20.; input name town team; run; proc print data=in; run;
'09'x
is the
hexadecimal representation of the tab character. The DSD option specifies
that two consecutive delimiters represent a missing value. The default
delimiter is a comma. For more information about the DSD option, see SAS System Options: Reference. The MISSOVER option prevents a SAS program from
going to a new input line if it does not find values in the current
line for all the INPUT statement variables. With the MISSOVER option,
when an INPUT statement reaches the end of the current record, values
that are expected but not found are set to missing.