Macro variable refereces will not resolve within single quotes, however, you can use macro quoting functions and other coding techniques to get around that limitation. I don't work with DDE that much, so my programming needs for examples have not required SAS macro programs. However, I believe if you perform a Google search using the string:
SAS Excel DDE macro program
you will come up with quite a few hits, this among them: http://analytics.ncsu.edu/sesug/2003/DM08-Li.pdf
If you're still working on it, here is how to use macro variables in your DDE commands:
Please note that
- ' has been replaced by "
- " has been replaced by ""
Another solution is to use the FILEVAR= option on the FILE statement. When writing to files on disk, FILEVAR= enables you to write to several files from one DATA step. The same is true when using FILEVAR= with DDE. It enables you to process several DDE commands from one DATA step.
Here is an example where Sheet1, Sheet2, and Sheet3 are filled with two columns and 10 rows of dummy data:
do i = 1 to 3;
sheet = cats('excel|[test-sheet.xlsx]Sheet', i, '!r1c1:r10c2');
file area dde filevar=sheet;
do x = 1 to 10;
y = 2*x;
put x y;
To me, this solution is simpler because there aren't any macros or macro variables involved.