SAS中文论坛

标题: 宏变两引用有问题，请指教。 [打印本页]

作者: shiyiming 时间: 2004-5-18 15:14
标题: 宏变两引用有问题，请指教。
我的程序如下，想把SAS数据导入EXCEL工作表。每个SAS数据库有一个数据。

options mlogic;
%macro sas2xl;
%do i=1 %to 9;
%do m=1 %to 6;
filename recrange dde 'excel|[jjg.xls]sheet1!r&i.c&m:r&i.c&m' notab;
data _null_;
set occu.expnum600&i&m;
file recrange;
put expnum;
run;
filename recrange clear;
%end;
%end;
%mend;
%sas2xl

可是有如下提示。

NOTE: The file RECRANGE is:
   DDE Session,
   SESSION=excel|[jjg.xls]sheet1!r&i.c&m:r&i.c&m,
   RECFM=V,LRECL=256
宏变量没有替换。请指教。多谢。

作者: shiyiming 时间: 2004-5-18 16:57
标题: Re: 宏变两引用有问题，请指教。
[quote="garyjjg":8f15f]
filename recrange dde 'excel|[jjg.xls]sheet1!r&i.c&m:r&i.c&m' notab;
[/quote:8f15f]

的单引号改成双引号就可以啦，嘿嘿~~~~

作者: shiyiming 时间: 2004-5-18 18:49
标题: 还是不行。
我换成双引号还是不行。

作者: shiyiming 时间: 2004-5-18 18:50
提示如下
filename recrange dde
1 ! ''excel|[jjg.xls]sheet1!r&i.c&m:r&i.c&m'' notab;    data _null_;    set
      -----
      23
ERROR 23-2: Invalid option name excel.

1 ! ''excel|[jjg.xls]sheet1!r&i.c&m:r&i.c&m'' notab;    data _null_;    set
                     ------
                     23
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable M resolves to 1
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable M resolves to 1
ERROR 23-2: Invalid option name sheet1.

作者: shiyiming 时间: 2004-5-18 19:39
在&M后面加.变成&M.

作者: shiyiming 时间: 2004-5-25 21:11
可是都不行啊。是不是在DDE中不能用宏变量。

作者: shiyiming 时间: 2004-5-25 23:40
I suggest you to turn on MPRINT options and see what kind of code has been generated by solving the macro variables. I think it is possible that the solved macro variables contain space after the number, so the column name became inappropriate. Just take an example, r1c1:r2c2 is not same as r1 c1 :r2 c2 .

作者: shiyiming 时间: 2004-5-26 16:08
the following is the reminder in the log:

filename recrange dde ''excel|[jjg.xls]sheet1!r&i.c1:r&i.c1'' notab;
                           -----
                           23
ERROR 23-2: Invalid option name excel.

1  !  filename recrange dde ''excel|[jjg.xls]sheet1!r&i.c1:r&i.c1'' notab;
                                          ------
                                          23
MPRINT(SAS2XL): filename recrange dde ''excel|[jjg.xls]sheet1!r1c1:r1c1'' notab;
ERROR: Error in the FILENAME statement.

ERROR 23-2: Invalid option name excel

ERROR 23-2: Invalid option name sheet1.

作者: shiyiming 时间: 2004-5-27 09:04
I tested the following program without any problem.  I am running SAS 8.1 in Winodw XP, and MS Office for XP.  Be attention, to run the program, you have to open Excel worksheet and let it run on background.

data a;
abc=123;
run;

options mlogic mprint macrogen;
%macro sas2xl;
%do i=1 %to 9;
%do m=1 %to 6;
filename recrange dde "excel|[sales.xls]sheet1!r&i.c&m:r&i.c&m" notab;
data _null_;
set a;
file recrange;
put abc;
run;
filename recrange clear;
%end;
%end;
%mend;
%sas2xl

作者: shiyiming 时间: 2004-5-27 09:14
Besides, your program is not a good way to accomplish your goal. A better way is to form a SAS data set or array with 9 rows and 6 columns, and write it to a spreadsheet at the end. You need to access the spreadsheet only once, not 54 times. With this SAS data set, if you can not make DDE access work, you can simply go to file/export data to save it into an Excel work sheet.

作者: shiyiming 时间: 2004-6-1 20:26
谢谢xic.
我想我的SAS可能有问题，总是无法实现上述功能。
但是我现在知道了你所说的更好的方法。

欢迎光临 SAS中文论坛 (http://mysas.net/forum/)