SAS输出结果能导出到excel中吗
关键词：sas结果数据导出excel,sas结果导出excel,sas 结果输出到excel
Right click your output file ( xxxx.lst) and choose '"open as ",then choose "Micrisoft office word",done!!!! 一个间接的方法就是把sas调整一下,让他在把结果输出到output窗口的同时产生一个网页形式的结果,然后在网页结果上面右键到导出到excel中,然后再进行相应修改.
用文本导入工具，很简单，我经常使用，大致介绍一下步骤：
1、选中所有需要存入excel的文本，复制；
2、新建excel，随便选中一个单元格，粘贴；
3、粘贴之后右下角会让你选择复制格式，选择“使用文本导入向导”
这样之后就是格式相对整齐的表格了
我是在sas9.2转入excel2007界面下操作的，别的版本没有尝试过。
可以先利用ods csvall输出为csv，然后自动转换成excel，比较麻烦。
- ods listing close;
- ods csvall body=’d:\tmp.csv’;
- proc means data=sashelp.class;
- var height;
- run;
- ods csvall close;
- ods listing;
- filename dst dde’excel|system’;
- data _null_;
- rc=system(‘start excel’);
- run;
- data _null_;
- x=sleep(5);
- run;
- data _null_;
- file dst;
- put ‘[open(“d:\tmp.csv”)]’;
- put ‘[save.as(“d:\tmp.xls”,1)]’;
- put ‘[file.close()]’;
- put ‘[quit()]’;
- run;
其他解答：
将数据集插入到指定的sheet页,还有一种笨方法 可以sas和VBA混合使用.通过sas调用excel已经写好的VBA宏.
下面是我自己以前写的一个宏,可能代码不是很好看.但是能实现基本的功能.适用于html,csv文件.默认excel文件和要导入到excel的文件存放在同一目录下.excel中有事先写好的宏.
SAS代码:
%let excelpath=E:\DDE\sasdatatoexcel\;
/*excel文件所在路径注意最后面的’\’符号*/
%macro toxls(excelname,sheetname,filename);
/*变量说明:*/
/*excelname-要导入数据的excel文件名*/
/*sheetname-sheet名*/
/*要导入到excel的文件名*/
/*打开excel程序*/
options noxsync noxwait xmin;
filename sas2xl dde ‘excel|system’;
data _null_;
length fid rc start stop time 8;
fid = fopen(‘sas2xl’,’s’);
if (fid le 0) then do;
rc = system(‘start excel’);
start=datetime();
stop=start+10;
do while (fid le 0);
fid= fopen(‘sas2xl’,’s’);
time=datetime();
if (time ge stop) then fid =1;
end;
end;
rc=fclose(fid);
run;
/*将文件导入到指定excel文件名的指定sheet*/
filename excel DDE “EXCEL|SYSTEM”;
filename export DDE “EXCEL|sheetname!r1c1:r100c100” notab;
data _null_;
file excel;
put “[open(“”&excelpath.&filename””)]”;
put “[open(“”&excelpath.&excelname””)]”;
put “[run(“”base””)]”;
run;
data _null_;
file export dlm=’09’x;
put “&sheetname”;
put “&excelname”;
put “&filename”;
run;
data _null_;
file excel;
put “[run(“”toxls””)]”;
put “[run(“”deletesht””)]”;
put “[save]”;
run;
%mend;
VBA代码:
Function WorksheetExists(wb As Workbook, sName As String) As Boolean
Dim s As String
On Error GoTo ErrHandle
s = wb.Worksheets(sName).Name
WorksheetExists = True
Exit Function
ErrHandle:
WorksheetExists = False
End Function
Sub crtsht()
Dim shtname As String
shtname = Sheets(“sheetname”).Cells(1, 1)
If WorksheetExists(ThisWorkbook, shtname) = False Then
Sheets.Add.Name = shtname
Else
Application.DisplayAlerts = False
Sheets(shtname).Delete
Sheets.Add.Name = shtname
End If
End Sub
Sub base()
Dim str As String
str = “sheetname”
If WorksheetExists(ThisWorkbook, str) = False Then
Sheets.Add.Name = str
Else
Sheets(str).Select
End If
End Sub
Sub deletesht()
If WorksheetExists(ThisWorkbook, “sheetname”) = True Then
Application.DisplayAlerts = False
Sheets(“sheetname”).Select
Cells.Select
Selection.Clear
Sheets(“sheetname”).Delete
End If
End Sub
Sub toxls()
Dim shtname As String
Dim excelname As String
Dim filename As String
shtname = Sheets(“sheetname”).Cells(1, 1)
excelname = Sheets(“sheetname”).Cells(2, 1)
filename = Sheets(“sheetname”).Cells(3, 1)
If WorksheetExists(ThisWorkbook, shtname) = False Then
Workbooks(filename).Sheets(shtname).Move After:=Workbooks(excelname).Sheets(ActiveWorkbook.Sheets.Count)
Else
Application.DisplayAlerts = False
Sheets(shtname).Delete
Workbooks(filename).Sheets(shtname).Move After:=Workbooks(excelname).Sheets(ActiveWorkbook.Sheets.Count)
End If
End Sub
| 每个过程都有自己的output或ODS,可以选择将结果输出到数据集中. 再用export过程可以直接输出. 而且可以在data步或sql过程中先整理成自己相要的格式. 我都是这样干的. |
有一个很好的方式把SAS中的数据规整地放入excel中。1.将SAS中的数据save as html 2.在保存的html中复制数据到excel就可以了。我用过，这样很规整的。
ods listing close;
ods results off;
ods html
path=’E:\cyh_work\2013年\8月份\20130819′
body=’result.xls’;
proc corr data=c.&var.new;
var &var &var._masking;
run;
proc freq data=c.&var.new;
tables length*first_&var;
run;
ods html close;
ods results on;
ods listing;
