数学建模社区-数学中国

标题: 提问:请问那本书有关于数据导入的详细介绍 [打印本页]

作者: jinsen78 时间: 2012-5-1 02:07
标题: 提问:请问那本书有关于数据导入的详细介绍
新手不怎么懂导入数据，有哪位高手赐教一下？

作者: hopeoflight 时间: 2012-5-2 10:41
一、将数据录入SAS——DATA Step / Viewtable
1.Internal raw data- Datalines or Cards 命令；
2.External Raw data files- Infile 命令 + Input 命令;

二、将数据文件读入SAS ——DATA Step / PROC IMPORT
1.将SAS文件读入SAS——
data sasuser.saslin;
   set "F:\sas1.sas7bdat";
run;
proc contents data=sasuser.saslin;
run;

2.将其他形式文件导入成SAS ——PROC IMPORT / 直接读入其他形式文件
proc import datafile = "c:\data\hsb2.sav" out= work.hsb2;
run;
proc contents data=hsb2;
run;
SAS导入数据：SAS recognizes the file type to be imported by file extension.

                  对数据长度的限制
在一些操作环境，SAS假定外部文件的纪录对最长为256（一行数据包括空格等所有字符在内的长度），如果预计读入的纪录长度超过256，可在Infile语句中使用LRECL=n 这个命令。
读入以空格作为分隔符的原始数据
如果原始数据的不同变量之间是以至少一个空格作为分隔符的，那可以直接采用List方法将这些数据读入SAS。
List Input读数据非常方便，但也有很多局限性：
（1）    不能跳过数据；
（2）    所有的缺失值必须以点代替
（3）    字符型数据必须是不包含空格的，且长度不能超过8；
（4）    不能直接读入日期型等特殊类型的数据。
程序举例：
INPUT Name $ Age Height;

                  读入按列组织的数据
有些原始数据的变量之间没有空格或其他分隔符，因此这样的文件不能以List形式对入SAS。但若不同变量值的都在每条记录的固定位置处，则可以按照Column 形式读入数据。Colunm读数据方法要求所有的数据均为字符型或者标准的数值型（数值中仅包括数字，小数点，正负号，或者是E，不包括逗号或日期型数据）。
相对于List方法，Column读数据方法有如下优点：
（1）    变量值之间无需用空格分开；
（2）    可以空格表示缺失值；
（3）    字符型数据中可包括空格；
（4）    可跳过数据。
程序举例：
INPUT Name $ 1-10 Age 11-13 Height 14-18;
使用格式命令读入非标准格式的数据
字符型数据： $informat w.
数值型数据： informat w.d
日期型数据： Datew.
(1)字符型：
$CHARw. ：不删除前后空格，读入字符数据；
$HEXw. ：将16进制的数据转化成字符数据；
$w.    ：删除前面空格，读入字符数据；
(2)日期，时间或日期时间型数据
DATEw.          ：以ddmmmyy或ddmmmyyyy形式读入日期；
DATETIMEw. ：以ddmmmyy hh:mm:ss.ss 形式读入日期时间；
DDMMYYw.    ：以ddmmyy或ddmmyyyy读入日期；
JULIANw.       ：以yyddd或yyyyddd读入Julia日期；
MMDDYYw.    ：以mmddyy或mmddyyyy形式读入日期；
TIMEw.          ：以hh:mm:ss.ss形式读入时间；
(3)数值型数据
COMMAw.d    ：读入数值型数据，将其中的逗号，$ 删除，并将括号转化为负号
HEXw.             ：将16进制数据转化成浮点型数据
IBw.d                ：读入整数二进制数据；
PERCENTw.    ：将百分数转化为普通数据；
w.d                   ：读入标准的数值型数据。

INPUT Name $16. Age 3. +1 Type $1. +1 Date MMDDYY10.
      (Score1 Score2 Score3 Score4 Score5) (4.1);
                     多种输入格式综合
读入位置控制——列指针
+n –n ：控制列指针从当前位置向前或向后移动n个字符；
@n ：控制列指针指向
举例：
INPUT ParkName $ 1-22 State $ Year @40 Acreage COMMA9.;

                     读入杂乱数据
在不确定从哪一列开始读入数据，但知道读入的数据均位于某一特定字符或字符串之后时，可采用@’character’列指针。
如：有字符串如下，需读入Breed：后面的字符串
My dog Sam Breed: Rottweiler Vet Bills: $478

（1）SAS 语句：Input @’Breed: ’ DogBreed $;    读入内容： Rottweil
读入Breed：后面的字符串，赋给DogBreed，读入时到空格时，自动结束。
（2）SAS 语句：Input @’Breed:’ DogBreed $20.; 读入内容：Rottweiler Vet Bill
读入Breed: 后面的字符串，赋给DogBreed，读入字符串的长度为20。
（3）SAS语句：Input @’Breed:’ DogBreed

20.; 读入内容：Rottweiler
   读入Breed: 后面的字符串，赋给DogBreed，读入字符串的长度为20，但遇到空格时不再继续读数据。
从原始数据中读入多行数据作为SAS的一条观测
使用行指针：
‘ / ’—— 到下一行读数据
‘#n ’——到第n 行读数据
INPUT City $ State $ / NormalHigh NormalLow #3 RecordHigh RecordLow;
从一行原始数据中读入多个观测
在Input语句末尾使用@@标示，告诉SAS继续读入本行后面的数据。
INPUT City $ State $ NormalRain MeanDaysRain @@;
有选择的读入原始数据
SAS让用户无需读入所有的原始数据，然后再判断是否是用户所需要的数据。用户仅需先读入足够的变量，以判断该条观测是否为自己所需，然后在INPUT语句后以@结尾，以使SAS读数据的指针停在此处，保留此行数据。然后用户使用IF语句判断读入的观测是否为所需数据，若是，则使用第二个INPUT语句继续读入其余数据。
INPUT Type $ @;
IF Type = ’surface’ THEN DELETE;
INPUT Name $ 9-38 AMTraffic PMTraffic;
@ & @@
(1) 均为锁定数据行的标示；
(2) @标示在SAS进入下个循环之前就释放锁定的数据行，而@@标示在继续锁定数据行
在INFILE语句中控制输入的选项
（1）FIRSTOBS=n : 从n条观测开始读入数据
（2）OBS=n 读入n条观测
（3）当读进内存的观测长度小于INPUT语句设定的长度时
当SAS指针到达一条记录的末尾，而在INPUT语句中尚有未读入的变量时，SAS默认继续读入下一行数据。
MISSOVER：不读入下一行数据，而将未赋值的变量以缺失值填充。
TRUNCOVER：当使用column或格式化读入方式时，某些数据行长度小于其他数据行长度时，使用TRUNCOVER选项，可防止SAS读入下一行数据。
使用DATA步读入分隔符文件
在INFILE语句中使用DLM= 选项或者DSD选项可以读入以特定符号作为分隔符的原始文件。
（1）The DLM= option （i.e. DLM=’&’）
如果是以Tab作为分隔符，则使用DLM=’09’X命令
（2）The DSD option：主要有三个功能
忽略单引号内的分隔符；
不将引号作为数据读入SAS；
将一行内连续两个单引号作为一个缺失值处理。
使用IMPORT程序步读入分隔符文件
IMPORT 程序的功能
（1）    自动扫描数据文件，并确定变量的类型（数值型或字符型）；
（2）    为字符型变量，自动设定变量的长度；
（3）    识别一些日期型数据；
（4）    将两个连续的分隔符作为一个缺失值读入SAS
（5）    读入引号内数据
（6）    自动将原始数据中不存在的变量赋缺失值；
PROC IMPORT DATAFILE=’filename’ OUT=data-set;
SAS根据读入文件的扩展名确定文件的类型。若读入文件没有正确的扩展名，或者是DLM文件，用户必须在IMPORT程序步中使用DBMS=option 选项。当读入数据集的名称已经存在于SAS库中，可用REPLACE选项将原数据覆盖。
PROC IMPORT DATAFILE=’filename’ OUT=data-set DBMS=identifier REPLACE;
在默认情况下，IMPORT程序步将第一行数据作为变量的名称。若第一行数据并非变量名，可在IMPORT语句后使用GETNAMES=NO语句。
若IMPORT程序读入的是分隔符文件，默认分隔符为空格。若不是，则需使用DILIMITER=statement语句指定分隔符。
PROC IMPORT DATAFILE=’filename’ OUT=data-set
                        DBMS=DLM REPLACE;
   GETNAMES=NO;
   DELIMITER=’delimiter-character’;
RUN;
使用IMPORT程序步读入PC文件
PROC IMPORT DATAFILE=’filename’ OUT=data-set
   DBMS=identifier REPLACE;
列示SAS数据集的内容
PROC CONTENTS DATA=data-set;
CONTENTS程序步的功能是显示SAS对数据集的具体描述，主要内容有：
（1）    数据集描述
            数据集的名称；
            观测的数量；
            变量的数量；
            创建日期
（2）    变量描述
            变量类型；
            变量长度；
            变量的输出格式；
            变更的输入格式；
            变量标识。

实例：
1.读入逗号分隔数据：cars_novname.csv

Acura,MDX,SUV,Asia,All,"$36,945 ","$33,337 ",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820 ","$21,761 ",2,4,200,24,31,2778,101,172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990 ","$24,647 ",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195 ","$30,299 ",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755 ","$39,014 ",3.5,6,225,18,24,3880,115,197

proc import datafile="cars_novname.csv" out=mydata dbms=csv replace;
getnames=no;
run;

proc contents data=mydata;
run;

SAS creates default variable names as VAR1-VARn when variables names are not present in the raw data file.

2.读入制表键分隔的数据：
proc import datafile="cars.txt" out=mydata dbms=tab replace;
getnames=no;
run;
3.根据不同任务将不同的数据集永久保存到对应任务的文件夹下：
libname dis "c:\dissertation";
proc import datafile="cars.txt" out=dis.mydata dbms=dlm replace;
delimiter='09'x;
getnames=yes;
run;
3.读入空格键分隔的数据：
proc import datafile="cars_sp.txt" out=mydata dbms=dlm replace;
getnames=no;
run;
4.分隔符的终极例子：
Other kinds of delimiters
You can use delimiter= on the infile statement to tell SAS what delimiter you are using to separate variables in your raw data file. For example, below we have a raw data file that uses exclamation points ! to separate the variables in the file.

22!2930!4099
17!3350!4749
22!2640!3799
20!3250!4816
15!4080!7827
The example below shows how to read this file by using delimiter='!' on the infile statement.

DATA cars;
INFILE 'readdel1.txt' DELIMITER='!' ;
INPUT mpg weight price;
RUN;

PROC PRINT DATA=cars;
RUN;
As you can see in the output below, the data was read properly.

OBS MPG WEIGHT PRICE

1    22    2930    4099
2    17    3350    4749
3    22    2640    3799
4    20    3250    4816
5    15    4080    7827
It is possible to use multiple delimiters. The example file below uses either exclamation points or plus signs as delimiters.

22!2930!4099
17+3350+4749
22!2640!3799
20+3250+4816
15+4080!7827
By using delimiter='!+' on the infile statement, SAS will recognize both of these as valid delimiters.

DATA cars;
INFILE 'readdel2.txt' DELIMITER='!+' ;
INPUT mpg weight price;
RUN;

PROC PRINT DATA=cars;
RUN;
As you can see in the output below, the data was read properly.

OBS MPG WEIGHT PRICE

1    22    2930    4099
2    17    3350    4749
3    22    2640    3799
4    20    3250    4816
5    15    4080    7827

import缺陷及注意事项：
Proc import does not know the formats for your variables, but it is able to guess the format based on what the beginning of your dataset looks like. Most of the time, this guess is fine. But if the length of a variable differs from beginning to end of your file, you might end up with some truncated values.

重点语法-Infile options
For more complicated file layouts, refer to the infile options described below.

DLM=
The dlm= option can be used to specify the delimiter that separates the variables in your raw data file. For example, dlm=','indicates a comma is the delimiter (e.g., a comma separated file, .csv file). Or, dlm='09'x indicates that tabs are used to separate your variables (e.g., a tab separated file).

DSD
The dsd option has 2 functions. First, it recognizes two consecutive delimiters as a missing value. For example, if your file contained the line 20,30,,50 SAS will treat this as 20 30 50 but with the the dsd option SAS will treat it as 20 30 . 50 , which is probably what you intended. Second, it allows you to include the delimiter within quoted strings. For example, you would want to use the dsd option if you had a comma separated file and your data included values like "George Bush, Jr.". With the dsd option, SAS will recognize that the comma in "George Bush, Jr." is part of the name, and not a separator indicating a new variable.

FIRSTOBS=
This option tells SAS what on what line you want it to start reading your raw data file. If the first record(s) contains header information such as variable names, then set firstobs=n where n is the record number where the data actually begin. For example, if you are reading a comma separated file or a tab separated file that has the variable names on the first line, then use firstobs=2 to tell SAS to begin reading at the second line (so it will ignore the first line with the names of the variables).

MISSOVER
This option prevents SAS from going to a new input line if it does not find values for all of the variables in the current line of data. For example, you may be reading a space delimited file and that is supposed to have 10 values per line, but one of the line had only 9 values. Without the missover option, SAS will look for the 10th value on the next line of data. If your data is supposed to only have one observation for each line of raw data, then this could cause errors throughout the rest of your data file. If you have a raw data file that has one record per line, this option is a prudent method of trying to keep such errors from cascading through the rest of your data file.

OBS=
Indicates which line in your raw data file should be treated as the last record to be read by SAS. This is a good option to use for testing your program. For example, you might use obs=100 to just read in the first 100 lines of data while you are testing your program. When you want to read the entire file, you can remove the obs= option entirely.

A typical infile statement for reading a comma delimited file that contains the variable names in the first line of data would be:

INFILE "test.txt" DLM=',' DSD MISSOVER FIRSTOBS=2 ;

读入有缺失值的数据或者读入数值中含有分隔符的数据
DATA cars2;
length make $ 20 ;
INFILE 'readdsd.txt' DELIMITER=',' DSD ;
INPUT make mpg weight price;
RUN;

PROC PRINT DATA=cars2;
RUN;

48,'Bill Clinton',210
50,'George Bush, Jr.',180
DATA guys2;
length name $ 20 ;
INFILE 'readdsd2.txt' DELIMITER=',' DSD ;
INPUT age name weight ;
RUN;

PROC PRINT DATA=guys2;
RUN;

最经典例子：从某行开始读入数据
DATA cars2;
length nf 8;
INFILE 'F:\cars1.csv' DELIMITER=',' dsd MISSOVER firstobs=2 ; /* obs=20; would read just the first 20 observations from your file. */
INPUT nf zh hh xb cs IHA fj;
RUN;

PROC PRINT DATA=cars2;
RUN;

从FTP读入数据
read raw data via FTP in SAS?
SAS has the ability to read raw data directly from FTP servers. Normally, you would use FTP to download the data to your local computer and then use SAS to read the data stored on your local computer. SAS allows you to bypass the FTP step and read the data directly from the other computer via FTP without the intermediate step of downloading the raw data file to your computer. Of course, this assumes that you can reach the computer via the internet at the time you run your SAS program. The program below illustrates how to do this. After the filename in you put ftp to tell SAS to access the data via FTP. After that, you supply the name of the file (in this case 'gpa.txt'. lrecl= is used to specify the width of your data. Be sure to choose a value that is at least as wide as your widest record. cd= is used to specify the directory from where the file is stored. host= is used to specify the name of the site to which you want to FTP. user= is used to provide your userid (or anonymous if connecting via anonymous FTP). pass= is used to supply your password (or your email address if connecting via anonymous FTP).

FILENAME in FTP 'gpa.txt' LRECL=80
            CD='/local2/samples/sas/ats/'
            HOST='cluster.oac.ucla.edu'
            USER='joebruin'
            PASS='yourpassword' ;
DATA gpa ;
INFILE in ;
INPUT gpa hsm hss hse satm satv gender ;
RUN;

PROC PRINT DATA=gpa(obs=10) ;
RUN;

读入多个数据文件

quarter1.dat

1 120321 1236 154669 211326
1 326264 1326 163354 312665
1 420698 1327 142336 422685
1 211368 1236 156327 655237
1 378596 1429 145678 366578
quarter2.dat

2 140362 1436 114641 362415
2 157956 1327 124869 345215
2 215547 1472 165578 412567
2 204782 1495 150479 364474
2 232571 1345 135467 332567
quarter3.dat

3 140357 1339 142693 205881
3 149964 1420 152367 223795
3 159852 1479 160001 254874
3 139957 1527 163567 263088
3 150047 1602 175561 277552
quarter4.dat

4 479574 1367 155997 36134
4 496207 1459 140396 35941
4 501156 1598 135489 39640
4 532982 1601 143269 38695
4 563222 1625 147889 39556
filename year ('d:\quarter1.dat' 'd:\quarter2.dat' 'd:\quarter3.dat' 'd:\quarter4.dat');
data temp;
infile year;
input quarter sales tax expenses payroll;
run;
proc print data = temp;
run;

读取excel 数据集

Reading an Excel file into SAS
Suppose that you have an Excel spreadsheet called auto.xls. The data for this spreadsheet are shown below.

MAKE          MPG WEIGHT PRICE
AMC Concord 22 2930 4099
AMC Pacer    17 3350 4749
AMC Spirit    22 2640 3799
Buick Century 20 3250 4816
Buick Electra 15 4080 7827
Using the Import Wizard is an easy way to import data into SAS. The Import Wizard can be found on the drop down file menu. Although the Import Wizard is easy it can be time consuming if used repeatedly. The very last screen of the Import Wizard gives you the option to save the statements SAS uses to import the data so that they can be used again. The following is an example that uses common options and also shows that the file was imported correctly.

PROC IMPORT OUT= WORK.auto1
         DATAFILE= "C:\auto.xls"
         DBMS=EXCEL REPLACE;
   SHEET="auto1";
   GETNAMES=YES;
   MIXED=YES;
   USEDATE=YES;
   SCANTIME=YES;
RUN;
proc print data=auto1;
run;

Obs MAKE          MPG WEIGHT PRICE

1 AMC Concord    22    2930    4099
2 AMC Pacer       17    3350    4749
3 Amc Spirit       22    2640    3799
4 Buick Century    20    3250    4816
5 Buick Electra    15    4080    7827
First we use the out= statement to tell SAS where to store the data once they are imported.
Next the datafile= statement tells SAS where to find the file we want to import.
The dbms= statement is used to identify the type of file being imported. This statement is redundant if the file you want to import already has an appropriate file extension, for example *.xls.
The replace statement will overwrite an existing file.
To specify which sheet SAS should import use the sheet="sheetname" statement. The default is for SAS to read the first sheet. Note that sheet names can only be 31 characters long.
The getnames=yes is the default setting and SAS will automatically use the first row of data as variable names. If the first row of your sheet does not contain variable names use the getnames=no.
SAS uses the first eight rows of data to determine whether the variable should be read as character or numeric. The default setting mixed=no assumes that each variable is either all character or all numeric. If you have a variable with both character and numeric values or a variable with missing values use mixed=yes statement to be sure SAS will read it correctly.
Conveniently SAS reads date, time and datetime formats. The usedate=yes is the default statement and SAS will read date or time formatted data as a date. When usedate=no SAS will read date and time formatted data with a datetime format. Keep the default statement scantime=yes to read in time formatted data as long as the variable does not also contain a date format.
Example 1: Making a permanent data file
What if you want the SAS data set created from proc import to be permanent? The answer is to use libname statement. Let's say that we have an Excel file called auto.xls in directory "d:\temp" and we want to convert it into a SAS data file (call it myauto) and put it into the directory "c:\dissertation". Here is what we can do.

libname dis "c:\dissertation";
proc import datafile="d:\temp\auto.xls" out=dis.myauto replace;
run;
Example 2: Reading in a specific sheet
Sometimes you may only want to read a particular sheet from an Excel file instead of the entire Excel file. Let's say that we have a two-sheet Excel file called auto2.xls. The example below shows how to use the option sheet=sheetname to read the second sheet called page2 in it.

proc import datafile="auto2.xls" out=auto1 replace;
sheet="page2";
run;
Example 3: Reading a file without variable names
What if the variables in your Excel file do not have variable names? The answer here is to use the statement getnames=no in proc import. Here is an example showing how to do this.

proc import datafile="a:\faq\auto.xls" out=auto replace;
getnames=no;
run;
Writing Excel files out from SAS
It is very easy to write out an Excel file using proc export in SAS version 8. Consider the following sample data file below.

Obs MAKE             MPG       WEIGHT          PRICE
1    AMC                22          2930          4099
2    AMC                17          3350          4749
3    AMC                22          2640          3799
4    Buick             20          3250          4816
5    Buick             15          4080          7827
Here is a sample program that writes out an Excel file called mydata.xls into the directory "c:\dissertation".

proc export data=mydata outfile='c:\dissertation\mydata.xls' replace;
run;

SAS读入复杂分隔数据——字符长度不同，字符中间有空格作为间隔符
1.字符长度不同
data web;
length site $41;
input age site $ hits;
datalines;
12 http://www.site1.org/default.htm 123456
130 http://www.site2.com/index.htm 97654
254 http://www.site3.edu/department/index.htm 987654
;
proc print;
run;

Obs                   site                      age    hits

1    http://www.site1.org/default.htm             12 123456
2    http://www.site2.com/index.htm             130    97654
3    http://www.site3.edu/department/index.htm 254 987654

或者用
data web;
input age site & $41. hits;
datalines;
12 http://www.site1.org/default.htm 123456
130 http://www.site2.com/index.htm 97654
254 http://www.site3.edu/department/index.htm 987654
;
proc print;
run;

Obs age                   site                      hits

1    12 http://www.site1.org/default.htm          123456
2    130 http://www.site2.com/index.htm             97654
3    254 http://www.site3.edu/department/index.htm 987654

2.字符有多个单词，单词之间用空格隔开
data fruit;
infile 'C:\messy.txt' delimiter = ' ' dsd;
length fruit $22;
input zip fruit $ pounds;
proc print;
run;

Obs fruit                zip    pounds

1    apples, grapes kiwi 10034 123456
2    oranges             92626    97654
3    pears apple          25414 987654
或者
data fruit;
input zip fruit & $22. pounds;
datalines;
10034 apples, grapes kiwi 123456
92626 oranges 97654
25414 pears apple    987654
;
proc print;
run;

Obs    zip    fruit                pounds

1    10034 apples, grapes kiwi 123456
2    92626 oranges                97654
3    25414 pears apple          987654

没有格式库的情况下读入数据：
read a SAS data file when I don't have its format library
If you try to use a SAS data file that has permanent formats but you don't have the format library, you will get errors like this.

ERROR: The format $MAKEF was not found or could not be loaded.
ERROR: The format FORGNF was not found or could not be loaded.
Without the format library, SAS will not permit you to do anything with the data file. However, if you use options nofmterr; at the top of your program, SAS will go ahead and process the file despite the fact that it does not have the format library. You will not be able to see the formatted values for your variables, but you will be able to process your data file. Here is an example.

OPTIONS nofmterr;
libname in "c:\";

PROC FREQ DATA=in.auto;
TABLES foreign make;
RUN;
高效的保留或者去掉部分变量的方式：
The following program creates exactly the same file, but is a more efficient program because SAS only reads the desired variables.

DATA auto2;
SET auto (KEEP = make mpg price);
RUN;
The drop data step option works in a similar way.

DATA AUTO2;
SET auto (DROP = rep78 hdroom trunk weight length
                  turn displ gratio foreign);
RUN;

比较双份录入的差异是否存在：
proc compare base = person1 compare = person2 brief;
by id;
id id;
run;

欢迎光临数学建模社区-数学中国 (http://www.madio.net/)