SAS与EXCEL互操作

晚风

SAS可以通过DDE (Dynamic Data Exchange) 与Excel交互，实现向Excel的写操作，如果再结合X4ML (Excel version 4 Macro Language) ，还可以对Excel进行包含各种设置在内的其他操作。在继续讨论Excel的操作之前，我们先明确一个SAS语句，那就是X及与其相关的两组全局选项xwait/noxwait和xsync/noxsync。X语句用于在SAS中执行windows程序，xwait/noxwait选项用于告诉X语句，执行windows程序后，是否要等待windows程序退出才继续sas程序，默认是xwait，就是等待windows程序退出，此时如果用X语句执行windows程序，会得到带有The  X command is active. Enter EXIT at the prompt in the X command window to reactivate this SAS session的对话框，只有windows程序退出，SAS程序才会继续，noxwait则不等待windows程序的退出而继续。xsync/noxsync选项是指定SAS程序和windows程序是同步执行还是异步执行，默认是xsync，就是SAS程序会等待windows全部完成后才继续执行，但如果noxsync，则SAS不会等待windows程序而继续执行。

下面是一段示例代码，注释部分对Excel的操作进行了说明，此段代码的功能是将若干与日期相关的数据集合并到一起，并将此vintage输出到Excel中以便查看和进一步处理：

options errors=4 compress=yes noxwait noxsync;
%macro write2excel (path=, file=, property=, saveas=);
/*merge all info into one vintage table*/
proc sql;
create table tmp
as
select coalesce(A.Date1, I.Datei) as Date, A.Count as Count1, Count2, Count3, Count4, Count5, Count6, Count7, Count8 from
vin.&property._1_freq as A full outer join
  (select coalesce(B.Date2, J.Datej) as Datei, B.Count as Count2, Count3, Count4, Count5, Count6, Count7, Count8 from
  vin.&property._2_freq as B full outer join
    (select coalesce(C.Date3, K.Datek) as Datej, C.Count as Count3, Count4, Count5, Count6, Count7, Count8 from
    vin.&property._3_freq as C full outer join
      (select coalesce(D.Date4, L.Datel) as Datek, D.Count as Count4, Count5, Count6, Count7, Count8 from
      vin.&property._4_freq as D full outer join
        (select coalesce(E.Date5, M.Datem) as Datel, E.Count as Count5, Count6, Count7, Count8 from
        vin.&property._5_freq as E full outer join
          (select coalesce(F.Date6, N.Daten) as Datem, F.COunt as Count6, Count7, Count8 from
          vin.&property._6_freq as F full outer join
            (select coalesce(G.Date7, H.Date8) as Daten, G.Count as Count7, H.Count as Count8 from
            vin.&property._7_freq as G full outer join
            vin.&property._8_freq  as H on G.Date7=H.Date8)
            as N on F.Date6=N.Daten)
          as M on E.Date5=M.Datem)
        as L on D.Date4 = L.Datel)
      as K on C.Date3=K.Datek)
    as J on B.Date2=J.Datej)
  as I on A.Date1=I.Datei
;
/*calculate how many rows (dates) the data has*/
select count(*)+4 into :rownum from tmp;
quit;

%let varlist =
Date
… …
;
/*start excel to make it ready to interact with SAS*/
X '"C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"';
data _null_;
    /*waiting for excel startup completely*/
    rc=sleep(5);  
run;
/*define file which refers to excel  by using dde*/
filename commands dde "excel|system";
data _null_;
    file commands;
    /*use x4ml to notice excel to run open command to open */
    put "[open(""&path.&file..xls"")]";
run;
/*due to rownum having many spaces ahead of the number, use mathematic express to remove all those spaces. If using rownum, r4c2:r&rown.c11 will be r4c2:r       10c11.*/
%let rown = %eval(&rownum. + 0);
/*define file which refers to the opened excel file [&file..xls] and its relevant cells’ range by using dde*/
filename excel dde "excel|[&file..xls]&property.!r4c2:r&rown.c11";
data _null_;
    set tmp;
    /*sas use 1960/01/01 as starting date to store date variable, but excel use 1989/12/31, so here do conversion*/
    Date = Date + 21915;
    file excel;
    /*output data into excel*/
    put &varlist.;
run;
data _null_;
    file commands;
    /*if yes, save this excel file into a new one and name it*/
    if &saveas. = "yes" then
         put "[save.as(""&path.&file._&sysdate..xls"")]";
    /*close file*/
    put "[file.close()]";
    /*exit excel application*/
    put "[quit()]";
run;
data _null_;
    /*wait for excel application close completely*/
     rc=sleep(5);
run;
%mend write2excel;
/*call macro to produce vintage excel file*/
%write2excel(path=***, file=Data_Vintage, property=*, saveas="yes");
%write2excel(path=***, file=Data_Vintage_&sysdate., property=*, saveas="no");
%write2excel(path=***,, file=Data_Vintage_&sysdate., property=*, saveas="no");