topic Re: Macro variable used in a cell range definition using DDE in SAS Programming

Macro variable used in a cell range definition using DDE

keyreal — Sat, 07 Aug 2010 00:30:58 GMT

Hello guys! I am trying to do the following:
1) Export a SAS data set to an Excel output file;
2) Open the same Excel output file from within SAS using DDE;
3) Select all data populated cells;

My problem comes when I try to use a macro variable &r (which resolves to the total number of rows including column headers) in defining the cell range selection. When I run the program I get an error which is shown below from my SAS log.
Any ideas how to fix this problem will be greatly appreciated! Thanks in advance!
***************************************************************************************
61 options symbolgen;
62 %let today = %qsysfunc(today(),date9.);
63 %let file = "C:\SAS Tests\testexcel as of &today..xls";
SYMBOLGEN: Macro variable TODAY resolves to 06AUG2010
64 data test;
65 input Name $ Age Country $;
66 cards;

NOTE: The data set WORK.TEST has 4 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

71 ;
72 run;
73 proc export data = test outfile=&file replace; sheet = 'people'; run;
SYMBOLGEN: Macro variable FILE resolves to "C:\SAS Tests\testexcel as of 06AUG2010.xls"

NOTE: File "C:\SAS Tests\testexcel as of 06AUG2010.xls" will be created if the export process
succeeds.
NOTE: "people" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.85 seconds
cpu time 0.29 seconds

74 options noxwait noxsync;
75 data _null_;
76 rc=system('start excel');
77 run;

NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

78 data _null_;
79 x=sleep(8);
80 run;

NOTE: DATA statement used (Total process time):
real time 8.23 seconds
cpu time 0.23 seconds

81 filename cmds dde 'excel|system';
82 data _null_;
83 file cmds;
SYMBOLGEN: Macro variable FILE resolves to "C:\SAS Tests\testexcel as of 06AUG2010.xls"
84 put "[open("&file")]";
85 x=sleep(5);
86 run;

NOTE: The file CMDS is:
DDE Session,
SESSION=excel|system,RECFM=V,LRECL=256

NOTE: 1 record was written to the file CMDS.
The minimum record length was 52.
The maximum record length was 52.
NOTE: DATA statement used (Total process time):
real time 5.60 seconds
cpu time 0.17 seconds

87 data _null_;
88 set test NOBS=rows;
89 call symput ('rows',rows);
90 run;

NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
89:21
NOTE: There were 4 observations read from the data set WORK.TEST.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

SYMBOLGEN: Macro variable ROWS resolves to 4
91 %let r = %eval(&rows + 1);
92 data _null_;
93 file cmds;
94 put '[workbook.activate("people")]';
95 put "[select("R1C1:R&rC3")]";
----------
49
WARNING: Apparent symbolic reference RC3 not resolved.
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

96 run;

NOTE: Variable R1C1 is uninitialized.
NOTE: Variable R is uninitialized.
NOTE: Variable rC3 is uninitialized.
NOTE: The file CMDS is:
DDE Session,
SESSION=excel|system,RECFM=V,LRECL=256

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.
Aborted during the EXECUTION phase.
NOTE: 1 record was written to the file CMDS.
The minimum record length was 29.
The maximum record length was 29.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Re: Macro variable used in a cell range definition using DDE

Cynthia_sas — Sat, 07 Aug 2010 02:14:16 GMT

Hi:
The error message is quite clear:

WARNING: Apparent symbolic reference RC3 not resolved.

Because when the word scanner encountered
[pre]
R&rC3
[/pre]

The word scanner interpreted the macro variable as: &RC3 and so it went to the GLOBAL symbol table and did not find &RC3 so the macro word scanner gave you a warning and the unresolved macro variable reference went forward to the code compiler. You need to delimit the &R, so that the constant C3 is not considered part of the macro variable name. A single period is used as a delimiter to tell the macro word scanner when to stopd reading characters and to start resolving. So the correct reference should have been:
[pre]
R&r.C3
[/pre]

The way the word scanner works is that the & is a macro trigger and everything between the & and the . is considered to be the macro variable reference to be looked up. Essentially, the single . disappears from the macro variable reference upon resolution. If you NEEDED to have a dot in a name, such as in a two level lib and dataset name, then you would use 2 periods, such as shown below:
[pre]
%let lib=sashelp;
%let dsn = class;
proc print data=&lib..&dsn;
run;
[/pre]

Consider this program, which duplicates your error message and shows the correct way to concatenate the 'C3' onto the &R value (program and log output both shown). Also, I believe that the correct command that you want to
generate is: [select("R1C1:R20C3")] with quotes around the row/column reference. Note that to accomplish that set of quotes -and- to have the macro variable resolved, you need to surround the row/column reference with doubled quotes, as in: putlog "[select(""R1C1:R&r.C3"")]";

cynthia
[pre]
632 data _null_;
633 set sashelp.class NOBS=rows;
634 call symput ('rows',rows);
635 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
634:23
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

636 %put rows= &rows;
rows= 19
637
638 %let r = %eval(&rows + 1);
639 %put r= &r;
r= 20
640 %put ***** Note the use of the . (period) to indicate the end of the macro variable reference;
***** Note the use of the . (period) to indicate the end of the macro variable reference
641 %put ***** Resolved macro variable reference: r1c1:r&r.c3 ;
***** Resolved macro variable reference: r1c1:r20c3
642 %put ***** INCORRECT: r1c1:r&rc3;
WARNING: Apparent symbolic reference RC3 not resolved.
***** INCORRECT: r1c1:r&rc3
643
644 data _null_;
645 putlog '***** Test resolution of Macro variable *****';
646 putlog '***** Note the extra double quotes around the values in the SELECT command';
647 putlog '[workbook.activate("people")]';
648 putlog "[select(""R1C1:R&r.C3"")]";
649 run;

***** Test resolution of Macro variable *****
***** Note the extra double quotes around the values in the SELECT command
[workbook.activate("people")]
[select("R1C1:R20C3")]
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[/pre]

Re: Macro variable used in a cell range definition using DDE

keyreal — Sat, 07 Aug 2010 03:25:42 GMT

Thanks a lot, Cynthia! Everything works fine now! 🙂