export 导出的excel sheet名字包含特殊字符

各位高手

proc export data=sashelp.class outfile="D:\class.xls";
sheet="班级-成绩@";
run;


导出的excel sheet名字中，短杆和@都被转换成了下划线，怎么保证特殊字符不被转换成下划线
比如我这里的-和@

急，谢谢各位

用DDE间接实现的，要是不弹出Excel窗口就更好。

1. options noxwait noxsync;
   
2. 
   
3. X 'del "D:\class.xls"';  
   
4. 
   
5. proc export data=sashelp.class outfile="D:\class.xls";
   
6.         sheet="sheet1";
   
7. run;
   
8. 
   
9. X '"D:\class.xls"';  
   
10. 
    
11. data _null_;
    
12. 
    
13.         z=sleep(1);
    
14. 
    
15. run;
    
16. 
    
17. filename cmds dde 'excel|system';
    
18. 
    
19. data _null_;
    
20. 
    
21.         file cmds;
    
22.         put '[workbook.insert(3)]';
    
23. 
    
24. run;
    
25. 
    
26. filename xlmacro dde 'excel|macro1!r1c1:r5c1' notab;
    
27. 
    
28. data _null_;
    
29. 
    
30.         file xlmacro;
    
31.                 put '=workbook.name("sheet1","班级-成绩@")';
    
32.                 put '=halt(true)';
    
33.                 put '!dde_flush';
    
34.         file cmds;
    
35.                 put '[run("macro1!r1c1")]';
    
36.                 put '[error(false)]';
    
37.                 put '[workbook.delete("macro1")]';
    
38.                 put '[save()]';
    
39.                 put '[quit()]';
    
40.                 z=sleep(1);
    
41. 
    
42. run;

复制代码

注：运行环境为 Office2003 + SAS 9.1.3

求币。

SAS 官网上有相似的问题可以参考
http://support.sas.com/kb/33/255.html

Problem Note 33255: Dashes mistranslated to underscores when using the IMPORT and EXPORT procedures in sheetnames
   

Dash characters are mistranslated to underscores when using the SHEET= option with the IMPORT and EXPORT procedure. In this example:

proc export data = sashelp.class
dbms = excel
file = "j:\mydata\testout.xls"
replace;
sheet = 'AE-MHX';
run;
The sheet created in EXCEL is named AE_MHX instead of AE-MHX.

The IMPORT procedure works in a similar way. In this example the IMPORT procedure will attempt to import a sheet with a dash character.

proc import file = "j:\mydata\testout.xls"
dbms = excel
out = test
replace;
sheet = 'AE-MHX';
run;
However the IMPORT procedure will actually look for AE_MHX as the sheetname.

To circumvent the problem, avoid dashes in your sheetname when creating the Excel file and rename the dash character to an underscore character when importing the file.

slimdell 发表于 2013-12-6 15:19
SAS 官网上有相似的问题可以参考
http://support.sas.com/kb/33/255.html

但是他没有回答问题啊，只是提出了问题

此题无解。

嗯，回去试试。

看了链接的网页，人家都这么说了

To circumvent the problem, avoid dashes in your sheetname when creating the Excel file and rename the dash character to an underscore character when importing the file.

那估计可能一定是无解了

要不就用DDE试试，不用proc import和export

You can use libname engine to work it out easily. Here is an example.

10   libname S EXCEL 'c:\temp\book0.xlsx' ;
NOTE: Libref S was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: c:\temp\book0.xlsx
11
12   data s.'tmp-35 1* () @'n;
13     set sashelp.class;
14   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set S.'tmp-35 1* () @'n has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


15
16   libname S clear;
NOTE: Libref S has been deassigned.

bobguy 发表于 2013-12-7 08:55
You can use libname engine to work it out easily. Here is an example.

10   libname S EXCEL 'c:\te ...

表示未能成功啊？？
55   libname S EXCEL 'd:\book1.xls' ;
NOTE: 已成功分配逻辑库引用名 S，如下所示:
       引擎:        EXCEL
       物理名: d:\book1.xls
56   options validvarname=any;
57
58
59    data s.'tmp-35 1* () @'n;
60      set sashelp.class;
61    run;

NOTE: SAS 变量标签、格式和长度并未写入 DBMS 表。
NOTE: 有 19 个从数据集 SASHELP.CLASS 读取的观测。
NOTE: 数据集 S.'tmp-35 1* () @'n 有 19 个观测和 5 个变量。
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.04 秒
      CPU 时间          0.01 秒


62
63    libname s clear;
NOTE: 已取消对逻辑库引用名 S 的分配。

bobguy 发表于 2013-12-7 08:55
You can use libname engine to work it out easily. Here is an example.

10   libname S EXCEL 'c:\te ...

表格中表名还是被SAS转义成-。