【SAS 小技巧】將資料以所需格式匯出到Excel

這是一個很有趣的需求，如何將SAS資料以所需格式直接匯出到Excel中，而不需要在Excel中手動調整，例如所有的變項都以「文字」格式匯出，或是數字格式匯出但在Excel中依然要保持Z format，也就是數字前補零。

這次所要挑戰為將結果匯出到Excel為《圖1》的樣子。

《圖1》

依據慣例先建立一個虛擬資料檔，程式如下【程式1】

【程式1】

data dates;
input ID $ sex 1. date : mmddyy10. score1 :best8. score2 :best8. percent: percent8.1 ;
format date  yymmdd10. percent percent8.1;
return;
datalines;
0001 0 04/05/2014 1000 -105.5 5.61%
0002 0 12/12/2015 -2000 9921.2 100.0%
1003 0 06/07/1995 3000  1205 0.1%
1004 1 05/30/1987 0000  -98745.1 2.5%
;
run;

讀入SAS後，資料所呈現的格式於《圖2》

《圖2》

在匯入資料的程式，我在format上面使用一般常見的格式設定，文字以單純文格式設定、日期格式採用yymmdd10.設定，數字以Best8.格式設定，百分比以 Percent8.1設定。

如果以最簡單的 Proc export 語法會出該資料檔，匯出後的Excel資料表呈現於《圖3》，大致上還不錯，差別比較大的為百分比的格式不見了。

《圖3》

而我心中想要呈現的格式應為《圖1》，ID保持文字格式；SEX保持單純數字格式；DATE採用YYYYMMDD10的格式（這樣才可以對齊啊）；SCORE1要有千位符號，補0至千位數，且負值以紅色表示零以藍色表示；SCORE2同樣也要有千位符號，且小數點後1位要補零，負值以紅色表示；PERCENT要保持百分比格式，且要補零至三位，小數點後2位均要補零。

《圖1》為了方便閱讀，重複此圖

為了達成《圖1》的樣子，這裡將使用ODS TAGSETS.EXCELXP匯出所需要的Excel檔案，並以Proc Report設定匯出時所需要的格式，而格式設定則是採用Excel所使用格式設定。

先從簡單的ODS TAGSETS.EXCELXP和Proc Report語法開始，我想【程式2】非常容易瞭解，就是用 TAGSETS.EXCELXP將Proc Report包裹起來，標準的ODS用法之一，這裡要注意是TAGSETS.EXCELXP所輸出的格式雖然為XML但不是Excel 2007之後的XML版本，因此輸出的Excel的副檔名必須為XLS。

Proc Report中的column就是宣告哪些變項要輸出。輸出的結果於《圖4》。這裡有幾個特別的地方，ID前兩筆前置的零不見了，PERCENT的百分比有留下。

【程式2】

ods tagsets.excelxp file="c:\temp\excel_test1.xls" style=sasweb;

proc report data=dates nowd ;
column id sex date score1 score2 percent ;
run;

ods _all_ close;

《圖4》

接下來先設定ID、SEX和DATE這三個變項輸出所需要的格式【程式3】。語法define就是宣告哪些變項需要做一些設定，我就直接針對重點說明，在ID後面可以看到「tagattr="format:@"」，每個變項所需格式不同的地方，其實就是將Excel的會使用到的格式放在「format:」後面，例如這裡的「@」在Excel中為文字格式。想要瞭解更多Excel自訂格式，可以在Google中搜尋『Excel 自訂格式』

「width=.55in」為設定輸出後在excel中的「欄寬」。SEX後面我們沒有設定格式只有調整了欄寬，而DATE後面則使用了「yyyy/mm/dd」的格式，並把欄寬稍微加寬。輸出的結果於《圖5》。

【程式3】

ODS TAGSETS.EXCELXP file="c:\temp\excel_test2.xls" style=sasweb;

proc report data=dates nowd ;
column ID sex date score1 score2 percent ;
define ID /display style(column)={tagattr="format:@" width=.55in};
define sex /display style(column)={width=.55in};
define date /display style(column)={tagattr="format:format:yyyy/mm/dd" width=.85in};
run;

ods _all_ close;

《圖5》

最後三個變項 SCORE1、SCORE2和PERCENT因有負值同時要設定顏色格式會稍微複雜一點。

先針對EXCEL的格式做一個簡單說明，其數字格式可以四個區隔，分別代表：
<正值>;<負值>;<零>;<文字>
有這個基本知識後，我想很快就可瞭解【程式4】所設定的格式了，格式設定細節其他網友寫的文章會比我說明還清楚，在此就不贅述了。【程式4】輸出的結果就是《圖1》了。

【程式4】 ODS TAGSETS.EXCELXP file="c:\temp\excel_test2.xls" style=sasweb;
proc report data=dates nowd ;
column ID sex date score1 score2 percent ;
define ID /display style(column)={tagattr="format:@" width=.55in};
define sex /display style(column)={width=.55in};
define date /display style(column)={tagattr="format:format:yyyy/mm/dd" width=.85in};
define score1 /display style(column)={tagattr="format:0,000;[Red]-0,000;[Blue]0,000;" width=.85in};
define score2 /display style(column)={tagattr="format:#,###.0;[Red]-#,###.0;[Blue]0;" width=.85in};
define percent /display style(column)={tagattr="format:000.00%" width=.95in};
run;
ods _all_ close;

最後提供「Don't Gamble with Your Output: How to Use Microsoft Formats with ODS」這篇好文章，我從這份文章學到很多新東西。大家看完後，可以試試看有沒辦法輸出《圖6》的結果。（可能還需要SAS HELP或其他文章幫忙）

如有誤請指教。轉載請註明來源。