BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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)")]';?
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
DanielSantos
Barite | Level 11
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
JasonS_SAS
SAS Employee
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 867 views
  • 0 likes
  • 4 in conversation