利用Libname匯入excel資料

原文出處:http://www2.sas.com/proceedings/sugi31/020-31.pdf

---

對於外部資料如excel要匯入SAS很麻煩嗎~~
一般來說不是利用SAS的proc import語法就是利用SAS的匯入選單,但有很多個檔案怎麼辦呢,上述SUGI技術文件ED heaton, Westat作者提供另外說明方便使用者在EXCEL匯入的方面語法

首先 先建立libname檔案,並且將該路徑資料夾中excel檔打上完整檔名,如file.xls

libname test "\\路徑資料夾\file.xls";

匯入後,就會在SAS的explorer裡面看到該test資料櫃,並在裡面看到file的excel檔案,並會有該檔案的所有可能分頁
下圖為excel檔案,欲匯入SAS程式內,其中有3個分頁sheet1,sheet2,sheet3


利用libname 匯入後,在SAS的explorer所呈現如下圖



打開該分頁檔案後,初步的excel匯入就大功告成了


但怎麼引用該資料檔呢? 在使用不同資料櫃資料檔時都必須先打上該資料櫃名稱,如test.file,或是SAS預設值work.file
而要引用剛剛匯入的excel檔的話則是將後面該資料檔名做個符號"filename"n包括起來,如下

test."Sheet1$"n;

例如想把剛剛匯入的excel做簡單的列印可寫為下述

Proc print data=test."Sheet1$"n Label; Run;

如下圖

---

對於excel匯入資料後若excel檔案中第一行為變項名稱時(特別是非英文命名時),SAS都會將其轉為SAS檔中的標籤部分Label,但對於使用者來說若不想要這樣的檔案名稱,可將其抹除(有一點風險在,就是每個變項命名使用者必須自行記憶,所以若在excel檔第一行皆以英文命名時SAS會直接引用到變項部分(name))
所以可以在libname語法後接上dbSasLabel=none,之後檔案就不會在包含標籤部分Label了

libname test "\\路徑資料夾\file.xls" dbSasLabel=none;

---

而若是excel檔的第一行並非是變項名稱,而是直接為資料部分,所以可以在libname語法後接上header=no,不過使用時要注意第一行確實為資料,否則該資料若為數字型態該第一行會變成Missing Data

libname test "\\路徑資料夾\file.xls" header=no;

如下圖為excel第一行為資料

匯入SAS後如下

若匯入的資料第一行是為變項時,接上header=no此語法會有錯誤出現,如下圖

第一行為變項,被判讀為該變項的資料,之後造成格式錯誤,故使用請小心

---

然而若excel資料屬性複雜(excel的通病,會有通用格式這種含糊不清的變項屬性)時
如下圖

所以在匯入SAS時會有錯誤出現,例如匯入後列表出來

libname test "C:\.........\demo.xls" ; Proc print data=test."Sheet1$"n label; Run ;

如下圖

文字變項中錯誤格式則顯示為空白(文字型的遺漏值(Missing value)),而數字型態的錯誤格式則顯示為一個點"."(數字型態的遺漏值)

而要解決這樣的問題時,其實可以將有含混不清的變項都視為文字型態
故可加入此語法mixed=yes,如下

libname test "C:\.........\demo.xls" mixed=yes ; Proc print data=test."Sheet1$"n label; Run ;

完成如下圖,在此可以先習慣一下資料屬性簡易辨別,文字型資料在SAS或excel都是在格子內站左邊,數字型站右邊,所以下圖性別和年齡都是文字格式了

---

---

以下為進階用法,請自行斟酌使用

---

---

而如要簡略修改某變項屬性格式時可試著引用(dbSASType=("name"="char(n)"))

Proc print data=test."Sheet1$"n(dbSASType=("name"="char(1)")); Run ;

其中"name"為需要更改的變項標籤(Label),如"性別"
而後面在加上"char(1)",其中的數字為文字屬性char字元長度為1,
而也可以改n字元長度(n自定數字)

此可用在任一引用資料後,例如

data test; set work.data(dbSASType=("name"="char(1)"));, Run ; 或是 proc print data=work.data(dbSASType=("name"="char(1)"));, Run ;

不過也可以將"char(n)"改為"NUMBERIC"或是"DATETIME","DATE","TIME"等

注意使用上述語法必須小心,特別是轉為數字型態"NUMBERIC"這類時容易有錯亂情形(雖然被宣告為文字型態但想轉回數字時該變項含有其他非數字的觀察值)
若想將變項宣告為數字型態時可改為以下寫法,才能讓數字中有文字的轉為遺漏值"."

如下為excel檔


寫入以下語法後將資料含有文字的變項皆轉為文字型態

libname xls "C:\.................\demo.xls" mixed=yes; proc print data=xls."sheet1$"n label; run;

SAS匯入資料如下圖(身高變項為_col2 體重變項為_col3)


欲將身高變項(_col2)與體重變項(_col3)再次轉回數字型態,並不考慮觀察值內的其他資料如M,S這些文字,而是一率都看成遺漏值"."
可在引用資料時另增新變項來求得該變項轉為數字型態的變換

data test; set xls."sheet1$"n ; x = input( _col2 , best. ) ; y = input( _col3 , best. ) ; proc print; Run ;

其中x來自身高變項(_col2)的轉換,其格式為SAS best12.的數字型態
y來自體重變項(_col3)的轉換,其格式也為SAS best12.的數字型態
列出後如下圖

---

此外excel檔通常也能支援日期格式的資料,固然匯入SAS時也是一大挑戰
如下圖為三種日期格式,1.原文提供之日期變數2.為時間變數3.為日期與時間混合變數
4.為excel中可接受的日期格式5.為excel中定義的日期變數


若將其直接讀入時,1,2,3個變項因為定義都只視為文字型態變項,但4,5因有excel的定義所以讀入SAS的格式能夠確實轉為時間格式date.


但1,2,3個變像是為時間格式但要如何匯入SAS呢?可打上此語法stringDates=yes,不過若是使用excel日期格式時皆會被轉回文字型態

libname xls "C:\.................\demo.xls" mixed=yes stringDates=yes; proc print data=xls."sheet1$"n label; run;

匯入後如下圖,時間變項皆被轉為文字型態


若要1,2,3確實轉為時間格式還是要使用者確實了解SAS時間格式的設定方法才能正確引用
而可以將時間先轉為SAS內建的時間(1960年1月1日起始),以日為單位,之後再行應用

data test; set xls."sheet1$"n ; x1 = input( DateVar , mmddyy10. ) ; x2 = input( TimeVar , hhmmss. ) ; proc print; Run ;

對於時間格式的變換有興趣可自行參閱SAS說明或是相關工具書

---