以下为个人自创,有对SAS爱好者可联系:nkwill@hotmail.com,初学者勿打扰.
/**************************************
*
*此程序实现对多个数据集输入到一个EXCEL工作簿中,
*唯一遗憾的是SHEET排序为倒序
*
********************************************/
OPTION SYMBOLGEN;
DATA A;
DO I=1 TO 100;
OUTPUT;
END;
RUN;
DATA B;
DO I=101 TO 500;
OUTPUT;
END;
RUN;
DATA C;
DO I=10 TO 500;
OUTPUT;
END;
RUN;
DATA D;
DO I=20 TO 500;
OUTPUT;
END;
RUN;
/**********以下程序实现将一个或者两个SAS数据集运用DDE输出到同一个EXCEL工作簿不同工作表中*/
options noxsync noxwait xmin;
filename sas2xl dde 'excel|system';
%let tab='09'x;
%MACRO TIME;
data _null_;
length fid rc start stop time 8;
fid=fopen('sas2xl','s'); /*此处fid=0,因为并没有start excel*/
if (fid le 0) then do;
rc=system('start excel'); /*启动excel,并保持10秒,以便EXCEL宏有足够的时间控制来自SAS程序的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;
%mend;
%MACRO XLM;
data _null_;/*插入一个宏insert(3)*/
file sas2xl;
put '[workbook.next()]';
put '[workbook.insert(3)]';
run;
filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab ;
data _null_;
file xlmacro;
put '=workbook.name("sheet1","第一")';
put '=workbook.name("sheet2","第二")';
put '=workbook.name("sheet3","第三")';
put '=workbook.name("sheet4","第四")';
put '=halt(true)';
put '!dde_flush';
file sas2xl;
put '[run("macro1!r1c1")]'
;
run;
filename xlmacro clear;
%MEND XLM;
%MACRO SHEET(N);
filename recrange dde "excel|[tt.xls]sheet&N!r4c1:r65000c1" notab;
data _null_; /***写入第一个数据集*/
set &&D&N;
file recrange;
put I
;
run;
filename recrange clear;
filename recrange dde "excel|[tt.xls]sheet&N!r3c1:r3c1" notab;
data _null_; /*写入标签值*/
file recrange;
put '手机号码'
;
run;
filename recrange clear;
data _null_; /*制作列宽,3表示自动按原字段值调整*/
file sas2xl;
put '[column.width(0,"c1:c1",false,3)]';
run;
data _null_;
file sas2xl;
put '[workbook.insert(1)]';
run;
%MEND SHEET;
%MACRO DDE1(d1,d2,d3,d4);
%TIME
data _null_; /*创建一个新工作簿;并删除原有的缺省值3张表;建立一个新表并保存*/
file sas2xl;
put '[file.close(false)]';
put '[new(1)]';
put '[error(false)]';
put '[save.as("D:\tt")]';
run;
%SHEET(1)
%SHEET(2)
%SHEET(3)
%SHEET(4)
%XLM
data _null_;
file sas2xl;
put '[workbook.delete("macro1")]';
put '[save]'; /*保存数据集*/
put '[file.close(false)]'; /*关闭文件*/
put '[quit]'; /*退出EXCEL程序*/
run;
%MEND DDE1;
%DDE1(A,B,C,D);