topic Re: Macros and DDE. in SAS Programming

Macros and DDE.

deleted_user — Mon, 07 Dec 2009 10:43:09 GMT

I could try, but maybe someone already have tried, and knows.

Is it possible to use macros in combination with DDE, when transferring data to Excel?

If the expression is: filename area 'excel|[XLWb.xls]Sheet1!r10c2:r20c30';

The next time it should be: filename area 'excel|[XLWb.xls]Sheet2!r10c2:r20c30';,

and so on. It would save code lines if there were 12 sheets and I could use a macro variable x, with values &x from 1 to 12.

Could there be Sheet&x inside ' ', or must the whole text line be generated?

And expressions like: put '[select("r10c1:r10c5")]';.

Is it then possible to change only the column numbers with a macro variable, let's say y, and have an expression:

put '[select("r10c1:r10c%eval(&y+5)")]';?

Re: Macros and DDE.

Cynthia_sas — Mon, 07 Dec 2009 15:05:31 GMT

Hi:
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

cynthia

Re: Macros and DDE.

deleted_user — Tue, 26 Jan 2010 11:42:08 GMT

If you're still working on it, here is how to use macro variables in your DDE commands:
put "[select(""r10c1:r10c%eval(&y+5)"")]";
Please note that
- ' has been replaced by "
- " has been replaced by ""
Good luck

Re: Macros and DDE.

DanielSantos — Tue, 26 Jan 2010 12:04:13 GMT

Hi

enclose the desired command into double quotes (for the macros inside it to be resolved), then follow this general rules for the enclosed text:

- two consecutive double ( "" ) quotes for each single one ( " )
- one single quote ( ' ) for each single one ( ' )

Some examples:
[pre]"""whatever"""
"'whatever'"
"""whatever"
"""""whatever"""""[/pre]
Which will resolve (same order) to:
[pre]"whatever"
'whatever'
"whatever
""whatever""[/pre]

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Macros and DDE.

JasonS_SAS — Tue, 26 Jan 2010 18:55:07 GMT

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:

[pre]
data _null_;
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;
end;
end;
run;
[/pre]

To me, this solution is simpler because there aren't any macros or macro variables involved.

Documentation for FILEVAR= option on FILE statement:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000171874.htm#a000699156