Tagsets.ExcelXP語句在SAS導(dǎo)出到Excel的方法應(yīng)用

熊勇超夏蘭芳宋渝丹陳 浩王 園王 林△

?

Tagsets.ExcelXP語句在SAS導(dǎo)出到Excel的方法應(yīng)用

熊勇超1夏蘭芳2宋渝丹3陳 浩1王 園1王 林1△

1.中國疾病預(yù)防控制中心政策研究與健康傳播中心（102206）

2.北京法瑪蘇提克咨詢有限公司

3.中國疾病預(yù)防控制中心科技處

SAS系統(tǒng)被譽(yù)為專業(yè)的統(tǒng)計(jì)分析標(biāo)準(zhǔn)軟件之一，具有強(qiáng)大的數(shù)據(jù)處理和數(shù)據(jù)分析功能［1-4］。當(dāng)有的用戶在操作系統(tǒng)平臺(tái)上沒有安裝SAS軟件時(shí)，傳遞和共享SAS數(shù)據(jù)或報(bào)表時(shí)會(huì)存在困難。Microsoft excel是在平時(shí)辦公中運(yùn)用十分廣泛的數(shù)據(jù)處理和分析軟件［5］，為更加有效地實(shí)現(xiàn)SAS數(shù)據(jù)或報(bào)表的傳遞和共享，可以通過將SAS數(shù)據(jù)或分析結(jié)果導(dǎo)出到Microsoft excel文件中。目前，我國還沒有相關(guān)文獻(xiàn)介紹該方法的應(yīng)用，本文將闡述如何采用ODS Tagsets.ExcelXP語句把SAS數(shù)據(jù)或報(bào)表導(dǎo)出為可以用Microsoft excel打開的文件。

語句介紹及用法

1.ODS

ODS是SAS Base軟件中可以控制輸出方法及輸出結(jié)果的形式，ODS對(duì)象的常用輸出目標(biāo)有OUTPUT、LISTING、HTML、RTF和PDF等。本文介紹ODS對(duì)象的輸出目標(biāo)之一Tagsets，控制其輸出結(jié)果為可以用excel打開的XML格式文件，同時(shí)采用Tagsets.ExcelXP可以創(chuàng)建包含有多個(gè)工作表的excel工作簿。

2.Tagsets.ExcelXP語法及應(yīng)用

下面基本的SAS代碼可用來創(chuàng)建excel工作簿：

①

ods _all_ close；

title J =C“Description”；

footnote J =C“Executed：&sysdate9.&systimeSAS Version：&sysver.”；

②

ods Tagsets.ExcelXP file =“d： est.xml”style =printer

③

options（embedded_titles =“yes”

embedded_footnotes =“yes”

autofilter =“1-2”

frozen_headers =“3”

frozen_rowheaders =“1”

absolute_column_width =“12”

autofit_height =“yes”

sheet_name =“Class”

proc print data =sashelp.class noobs label；

run；

④

ods Tagsets.ExcelXP close；

該程序的輸出結(jié)果為：

語句①是將ODS所有其他的輸出對(duì)象全部關(guān)閉，本程序只輸出可以用Excel打開的“xml”文件；語句②指定ODS Tagsets.ExcelXP對(duì)象，其中file =“d： test.xml”指定了存儲(chǔ)在本地磁盤目錄D下的可用excel打開的“xml”格式文件test。Style選項(xiàng)控制整個(gè)輸出文件的布局和格式，如字體，字號(hào)，背景顏色等，運(yùn)行以下SAS代碼可顯示SAS中ODS可用的所有Style名稱。

ods _all_ close；

ods listing；

當(dāng)問及“你知道超市中商品上的條形碼表示什么含義嗎？”時(shí)，國際貿(mào)易專業(yè)55%的學(xué)生表示知道，市場營銷專業(yè)84%的學(xué)生表示明白；當(dāng)問及“你知道如何來評(píng)定茶葉的質(zhì)量嗎？”時(shí)，國際貿(mào)易專業(yè)42%的學(xué)生表示知道，市場營銷專業(yè)79%的學(xué)生表示知道如何評(píng)定茶葉的質(zhì)量；當(dāng)問及“你知道如何來鑒別滌綸、錦綸、腈綸嗎？”國際貿(mào)易專業(yè)12%學(xué)生知道如何來鑒別這幾種紡織纖維，市場營銷專業(yè)46%的學(xué)生知道如何來鑒別這幾種紡織纖維。

proc template；list styles；run；quit；

語句③是用來控制excel工作簿的屬性和功能，在ODS語句中控制tagset選項(xiàng)是通過opions來實(shí)現(xiàn)的，Tagsets.ExcelXP可以實(shí)現(xiàn)許多的選項(xiàng)功能，運(yùn)行下面的SAS代碼，所有tagset選項(xiàng)輸出到SAS日志窗口中。

filename temp temp；

ods Tagsets.ExcelXP file =temp options（doc =′help′）；

ods Tagsets.ExcelXP close；

filename temp clear；

語句④是結(jié)束ods Tagsets.ExcelXP語句。

下面將重點(diǎn)介紹幾種常用的Tagsets選項(xiàng)，如語句③所示，本例中運(yùn)用了9個(gè)Tagsets選項(xiàng)。embedded_ titles和embedded_footnotes是分別用來指定是否輸出數(shù)據(jù)或報(bào)表的標(biāo)題和尾注，其默認(rèn)值“no”。當(dāng)需要輸出數(shù)據(jù)或報(bào)表的標(biāo)題和尾注，程序員除了要指定SAS程序中title和footnote語句，還要在ODS Tagsets選項(xiàng)中指定embedded_titles =“yes”和embedded_footnotes=“yes”；autofilter選項(xiàng)用來實(shí)現(xiàn)數(shù)據(jù)或報(bào)表的篩選功能，其默認(rèn)值為“none”，當(dāng)指定autofilter =“all”時(shí)，所有的列標(biāo)題右側(cè)均有一個(gè)篩選按鈕，有時(shí)需要某一列或某幾列有篩選按鈕，此時(shí)指定autofilter等于需要指定列的序號(hào)；有時(shí)為了查看數(shù)據(jù)或報(bào)表方便，需要凍結(jié)工作表的行和列，frozen_headers和frozen_rowheaders是用來實(shí)現(xiàn)該功能的，其默認(rèn)值為“no”。當(dāng)需要凍結(jié)工作表的前三行和左一列時(shí)，可指定frozen_headers =“3”和frozen_rowheaders =“1”來實(shí)現(xiàn)；absolute_column_width是用來指定輸出工作表列的絕對(duì)寬度，其默認(rèn)值為“none”；autofit_height，默認(rèn)值為“no”，指定為“yes”時(shí)可以自動(dòng)調(diào)整行高；sheet_name用來指定輸出工作表的名稱，默認(rèn)值為“none”；sheet_interval該選項(xiàng)控制輸出的excel工作簿中含有幾個(gè)工作表，默認(rèn)值為“table”單個(gè)工作簿中只會(huì)有一個(gè)工作表，當(dāng)指定為“bygroup”時(shí)by變量的每個(gè)值都會(huì)作為一個(gè)工作表，指定為“proc”時(shí)每個(gè)SAS過程輸出的內(nèi)容作為一個(gè)工作表。

3.單個(gè)工作薄中輸出多個(gè)工作表的SAS實(shí)現(xiàn)

下面這段程序可以實(shí)現(xiàn)在單個(gè)工作簿中輸出多個(gè)工作表的功能：

ods _all_ close；

title J =C“Description of Class”；

footnote J =C“Executed：&sysdate9.&systimeSAS Version：&sysver.”；

ods Tagsets.ExcelXP file =“d： est.xml”style =printer

options（embedded_titles =“yes”

embedded_footnotes =“yes”

autofilter =“1-2”

autofit_height =“yes”

frozen_headers =“3”

frozen_rowheaders =“1”

absolute_column_width =“12”）；

⑤

ods Tagsets.ExcelXP options（sheet_name =“Class _Female”

sheet_interval =“none”）；

proc print data =sashelp.class noobs label style （header）=［just =center］；

where sex =“F”；

run；

⑥

ods Tagsets.ExcelXP options（sheet_name =“Class _Male”

sheet_interval =“none”）；

proc print data =sashelp.class noobs label style （header）=［just =center］；

where sex =“M”；

run；

ods Tagsets.ExcelXP close；

該程序的輸出結(jié)果為：

單個(gè)工作簿中輸出多個(gè)工作表的實(shí)現(xiàn)方法有多種，正如上文所介紹的sheet_interval選項(xiàng)，當(dāng)指定為“bygroup”或“proc”時(shí)均可以實(shí)現(xiàn)該功能，但是要在單個(gè)工作簿中輸出多個(gè)工作表的同時(shí)分別指定每個(gè)工作表的名稱時(shí)，可以采用以上的SAS程序，如⑤和⑥所示對(duì)每個(gè)輸出的工作表分別指定sheet_name和sheet_ interval選項(xiàng)。

在實(shí)踐中往往會(huì)遇到將指定的整批SAS數(shù)據(jù)集導(dǎo)出為excel文件，以下這段程序可以幫助我們實(shí)現(xiàn)這一功能：

proc sql noprint；

create table cur_data as select memname from dictionary.tables

where libname =“SASUSER”order by memname；

select count（distinct memname）into：dscnt from cur_data；

%let dscnt =&dscnt；

select distinct memname into：ds1-：ds&dscnt from cur_data order by memname；

quit；

title J =C“Description”；

footnote J =C“Executed：&sysdate9.&systimeSASVersion：&sysver.”；

ods Tagsets.ExcelXP file =“d： test_many.xml”style =printer

options（embedded_titles =“yes”

embedded_footnotes =“yes”

autofilter =“1-2”

autofit_height =“yes”

frozen_headers =“3”

frozen_rowheaders =“1”

absolute_column_width =“12”）；

%macro sas2xls；

%do i =1%to &dscnt；

ods Tagsets.ExcelXP options（sheet _ name =“&&ds&i.”

sheet_interval =“none”）；

proc print data =sasuser.&&ds&i.noobs label style （header）=［just =center］；run；

%end；

%mend sas2xls；

%sas2xls；

ods Tagsets.ExcelXP close；

上面的程序可以把SAS軟件自帶的SASUSER庫中的所有數(shù)據(jù)集全部導(dǎo)出到D盤的test_many文件中；如果輸出該庫中除某個(gè)數(shù)據(jù)集外的所有數(shù)據(jù)集，可以將該不輸出數(shù)據(jù)集名字從cur_data中刪掉。

討 論

本文介紹的ODS Tagsets.ExcelXP語句是實(shí)現(xiàn)SAS數(shù)據(jù)或報(bào)表輸出到excel文件最基本、最常用的SAS程序。當(dāng)有其他格式要求時(shí)，可以通過設(shè)置語句中style和options選項(xiàng)控制excel輸出的各種外觀和功能，此外，其他相關(guān)內(nèi)容可以參考SAS相關(guān)文獻(xiàn)［6］。

單個(gè)excel工作簿中輸出多個(gè)工作表時(shí)，style選項(xiàng)（如文章中的style =printer）控制了整個(gè)excel文件的格式，如果在上段程序中⑤和⑥中再指定其他的style是無效的。在這種情況下，可以在具體的proc過程中指定style選項(xiàng)來實(shí)現(xiàn)該工作表的外觀屬性。在實(shí)踐中，如果想把某個(gè)文件夾中的所有數(shù)據(jù)集輸出為excel文件，可以將該文件夾路徑添加至SAS Library中，如命名為BJ，然后采用本文中的程序?qū)J庫中的所有數(shù)據(jù)集導(dǎo)出。

本文采用ODS Tagsets.ExcelXP語句輸出的文件后綴是“xml”，是可以用excel打開的，如果產(chǎn)生的是“xls”或“xlsx”文件，用excel 2007/2010打開時(shí)會(huì)彈出警告對(duì)話框，但仍可以打開。Ods Tagests.ExcelXP語句要求在SAS Base9.1.3及以上版本使用，并且要求excel 2002及其以后的版本。

將SAS數(shù)據(jù)導(dǎo)出到excel的方法除了上文所介紹的方法，還有其他方法，例如SAS菜單欄下導(dǎo)出數(shù)據(jù)選項(xiàng)、export過程、ods csvall語句、libname語句等等，但是這些方法僅限于數(shù)據(jù)集的導(dǎo)出，不能輸出SAS過程產(chǎn)生的結(jié)果，也不能實(shí)現(xiàn)對(duì)輸出文件的背景顏色、字體、字號(hào)、格式等excel外觀和功能的控制，這是ODS Tagsets.ExcelXP語句最大的優(yōu)點(diǎn)。ODS Tagsets.ExcelXP可以將數(shù)據(jù)集或者SAS過程的結(jié)果輸出到excel文件中，但是不支持圖像的輸出，因此不能與SAS/ GRAPH軟件的過程聯(lián)合使用［6］。

參考文獻(xiàn)

［1］王詩遠(yuǎn)，劉沛.流行病學(xué)調(diào)查報(bào)告數(shù)據(jù)標(biāo)準(zhǔn)化入口的SAS實(shí)現(xiàn).中國衛(wèi)生統(tǒng)計(jì)，2013，30（1）：114-118.

［2］李洪興，董國慶，王麗，等.SAS軟件在公共衛(wèi)生監(jiān)測(cè)數(shù)據(jù)核查中的應(yīng)用.中國衛(wèi)生統(tǒng)計(jì)，2012，29（5）：749，751.

［3］裴磊磊，任琳，高文龍，等.集中指數(shù)及可信區(qū)間的SAS實(shí)現(xiàn).中國衛(wèi)生統(tǒng)計(jì)，2013，32（3）：390-392.

［4］夏蘭芳，孟郁潔，李迎迎，等.多選題數(shù)據(jù)統(tǒng)計(jì)分析方法及SAS實(shí)現(xiàn).中國衛(wèi)生統(tǒng)計(jì)，2015，32（1）：163-164.

［5］宋廷山，王堅(jiān)，姜愛萍.應(yīng)用統(tǒng)計(jì)學(xué)：以EXCEL為分析工具.北京：清華大學(xué)出版社，2012.

［6］DelGobboV.Some Techniques for Integrating SAS Output with Microsoft Excel Using Base SAS.Proceedings of the SAS Global Forum 2013 Conference.Cary，NC：SAS Institute Inc.

（責(zé)任編輯：劉 壯）

通信作者：△王林，E-mail：wanglin@ chinacdc.cn