在C盘新建了一个名为book.xls的EXCEL文件,文件内容:
year | x1 | x2 | x3 | x4 | x5 | x6 | x7 | x8 |
1990 | 343.76 | 45.44 | 101.37 | 30.9 | 19.02 | 8.42 | 31.38 | 4.34 |
1991 | 349.03 | 49.92 | 99.71 | 34.55 | 21.84 | 10.04 | 35.62 | 5.15 |
1992 | 354.12 | 49.03 | 97.95 | 34.24 | 22.54 | 11.43 | 40.87 | 5.15 |
1993 | 366.95 | 45.44 | 87.7 | 36.68 | 22.31 | 14.3 | 47.94 | 10.73 |
1994 | 398.3 | 46.8 | 94.73 | 36.91 | 21.34 | 15.99 | 49.99 | 12.66 |
1995 | 435.14 | 50.86 | 103.21 | 38.79 | 24.06 | 19.12 | 58 | 13.06 |
1996 | 464.78 | 59.72 | 114.98 | 44.21 | 30.58 | 24.71 | 69.53 | 16.66 |
1997 | 455.95 | 56.03 | 119.45 | 43.74 | 31.98 | 27.61 | 75.89 | 17.55 |
1998 | 439.45 | 50.72 | 123.94 | 42.37 | 35.24 | 31.39 | 82.45 | 17 |
1999 | 435.34 | 48.33 | 122.19 | 43.2 | 36.77 | 36.09 | 88.39 | 18.03 |
2000 | 431.31 | 50.44 | 135.8 | 39.66 | 46.03 | 48.95 | 98.15 | 27.58 |
2001 | 433.25 | 51.47 | 145.54 | 40.15 | 50.39 | 57.36 | 100.47 | 29.43 |
2002 | 444.3 | 54.99 | 157.2 | 42.08 | 54.44 | 67.31 | 110.14 | 30.2 |
2003 | 456.74 | 56.84 | 158.97 | 42.09 | 59.67 | 83.78 | 121.49 | 22.17 |
2004 | 507.5 | 59.1 | 159.47 | 43.88 | 64.21 | 94.74 | 121.79 | 23.74 |
2005 | 559.42 | 71.53 | 178.19 | 53.62 | 117.93 | 142.24 | 80.91 | 26.23 |
现在把用SAS读取它,我们有以下几种途径(还有一些,待学):
1.LIBNAME STATEMENT
libname xlsfile 'c:/book.xls' mixed=no header=yes scantime=yes ;
/*mixed,是否将数值型变量转化为字符型变量*/
/*header,是否将excel中数据第一行设为sas型数据变量名*/
/*scantime,是否遍历查询日期型数据,自动设置日期数据的格式*/
2.PROC SQL PASS-THROUGH FACILITY
proc sql;
connect to excel (path="c:/book.xls");
create table book as
select *
from connection to excel (select * from [sheet1$]);
disconnect from excel;
quit;
3.IMPORT PROCEDURE
PROC IMPORT OUT= WORK.BOOK1
DATAFILE= "C:/Book.xls"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
4.关于xlsx等格式
现在越来越多的excel文件是xlsx格式的,这种excel文件读取方式类似以上情况。 另外也可以参考
http://support.sas.com/kb/32/455.html
关于同时读取多个excel文件,中文论坛有所讨论:
http://www.mysas.net/forum/viewtopic.php?f=4&t=7525&sid=575275d20d2c880255c2aaa0a0f85fda