sas读入数据全解析.doc

资料收集于网络，如有侵权请联系网站删除SAS数据步导入数据终极汇总经典，一看就会 一、将数据录入SASDATA 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:datahsb2.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=09X命令（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.csvAcura,MDX,SUV,Asia,All,$36,945 ,$33,337 ,3.5,6,265,17,23,4451,106,189Acura,RSX Type S 2dr,Sedan,Asia,Front,$23,820 ,$21,761 ,2,4,200,24,31,2778,101,172Acura,TSX 4dr,Sedan,Asia,Front,$26,990 ,$24,647 ,2.4,4,200,22,29,3230,105,183Acura,TL 4dr,Sedan,Asia,Front,$33,195 ,$30,299 ,3.2,6,270,20,28,3575,108,186Acura,3.5 RL 4dr,Sedan,Asia,Front,$43,755 ,$39,014 ,3.5,6,225,18,24,3880,115,197proc 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=09x; getnames=yes;run;3.读入空格键分隔的数据：proc import datafile=cars_sp.txt out=mydata dbms=dlm replace;getnames=no;run;4.分隔符的终极例子：Other kinds of delimitersYou 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!409917!3350!474922!2640!379920!3250!481615!4080!7827The 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 PRICE1 22 2930 40992 17 3350 47493 22 2640 37994 20 3250 48165 15 4080 7827It is possible to use multiple delimiters. The example file below uses either exclamation points or plus signs as delimiters.22!2930!409917+3350+474922!2640!379920+3250+481615+4080!7827By 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 PRICE1 22 2930 40992 17 3350 47493 22 2640 37994 20 3250 48165 15 4080 7827import缺陷及注意事项：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 optionsFor 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=09x 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,21050,George Bush, Jr.,180DATA 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= 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.dat1 120321 1236 154669 2113261 326264 1326 163354 3126651 420698 1327 142336 4226851 211368 1236 156327 6552371 378596 1429 145678 366578quarter2.dat2 140362 1436 114641 3624152 157956 1327 124869 3452152 215547 1472 165578 4125672 204782 1495 150479 3644742 232571 1345 135467 332567quarter3.dat3 140357 1339 142693 2058813 149964 1420 152367 2237953 159852 1479 160001 2548743 139957 1527 163567 2630883 150047 1602 175561 277552quarter4.dat4 479574 1367 155997 361344 496207 1459 140396 359414 501156 1598 135489 396404 532982 1601 143269 386954 563222 1625 147889 39556filename 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 SASSuppose that you have an Excel spreadsheet called auto.xls. The data for this spreadsheet are shown below.MAKE MPG WEIGHT PRICEAMC Concord 22 2930 4099AMC Pacer 17 3350 4749AMC Spirit 22 2640 3799Buick Century 20 3250 4816Buick Electra 15 4080 7827Using 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 PRICE1 AMC Concord 22 2930 40992 AMC Pacer 17 3350 47493 Amc Spirit 22 2640 37994 Buick Century 20 3250 48165 Buick Electra 15 4080 7827First 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 fileWhat if you want the SAS data set created from proc import to be permanent? The answer is to use libname statement. Lets 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:tempauto.xls out=dis.myauto replace;run;Example 2: Reading in a specific sheetSometimes you may only want to read a particular sheet from an Excel file instead of the entire Excel file. Lets 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 c import datafile=auto2.xls out=auto1 replace;sheet=page2;run;Example 3: Reading a file without variable namesWhat 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 c import datafile=a:faqauto.xls out=auto replace;getnames=no;run;Writing Excel files out from SASIt is very easy to write o