SAS Data Step 1b Reading Different Types of Data.ppt

1、Reading Different Types of Data,1,1,1,Content,Start with creating a SAS Data Set Using list input to read delimited raw data files Other Input Styles Input raw data in SAS Reading Excel Spreadsheets Variable Attributes,2,2,data SAS-data-set-name; infile raw-data-filename; input input-specifications;

2、run;,data SAS-data-set-name; infile raw-data-filename; input input-specifications;,data SAS-data-set-name; infile raw-data-filename;,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,Creating a SAS Data Set,DATA Step,In order to create a SAS data set from a raw data file,

3、 you must,start a DATA step and name the SAS data set being created (DATA statement),identify the location of the raw data file to read (INFILE statement),describe how to read the data fields from the raw data file (INPUT statement).,.,3,3,DATA Statement,Starts the DATA step and names the SAS data s

4、et being created. Syntax,DATA ,4,4,How to create temporary dataset permanent dataset one or more output data sets Example1,5,5,. Not Creating a Data Set Note: Usually, the DATA statement specifies at least one data set name that SAS uses to create an output data set. However, when the purpose of a D

5、ATA step is to write a report or to write data to an external file, you may not want to create an output data set. Using the keyword _NULL_ as the data set name causes SAS to execute the DATA step without writing observations to a data set.,LOG Info.,data _NULL_; input subjid $ sex$ age; put subjid

6、sex age; cards; 1001 M 35 1002 M 28 1003 F 30 1004 M 29 ; run;,LOG Info.,6,6,6,Content,Start with creating a SAS Data Set Using list input to read delimited raw data files Other Input Styles Input raw data in SAS Reading Excel Spreadsheets Variable Attributes,7,7,Data Type,Two common data types: Fix

7、ed Fields Delimited Data,8,8,Fixed Fields,In fixed columns. Arranged regularly.,9,9,Delimited Data,Not in fixed columns. Separated by the same delimiters.,Delimiters Blanks (default) Commas Tab characters,10,10,Input Style,Four basic Input Styles List Input Column Input Formatted Input Named Input.

8、Choose the Input style depending on the layout of data values in the records.,11,11,Using list input to read delimited raw data files,Syntax variable : names a variable that is assigned input values. $ :indicates that the variable has character values rather than numeric values.,INPUT variable ;,12,

9、12,Example,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,SAS Data Set,Data Step,13,13, holds the input record for the execution of the next INPUT statement within the same iteration of the DATA step. Tip: The trailing prevents the next INPUT statement from automatical

10、ly releasing the current input record and reading the next record into the input buffer. It is useful when you need to read from a record multiple times., holds the input record for the execution of the next INPUT statement across iterations of the DATA step. Tip: The double trailing is useful when

11、each input line contains values for several observations. Example2,14,14,PDV,Looking Behind the Scenes,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 10

12、06 NONSMOKER BLACK 38,Raw Data File,.,The default length of variables is 8 bytes.,15,15,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BL

13、ACK 38,Raw Data File,.,.,PDV,16,16,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,Raw Data File,PDV,.,1 0 0 1 E X S M O K E R A

14、S I A N 3 0,1001,EXSMOKER,ASIAN,30,17,17,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,Raw Data File,PDV,.,1001,EXSMOKER,ASIAN,

15、30,Implicit output,1 0 0 1 E X S M O K E R A S I A N 3 0,18,18,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,Raw Data File,PDV,

16、.,1001,EXSMOKER,ASIAN,30,Implicit return,1 0 0 1 E X S M O K E R A S I A N 3 0,19,19,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK

17、 38,Raw Data File,.,PDV,.,1 0 0 1 E X S M O K E R A S I A N 3 0,20,20,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,Raw Data Fi

18、le,.,PDV,1002,NONSMOKE,BLACK,54,1 0 0 2 N O N S M O K E R B L A C K 5 4,21,21,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,Raw

19、 Data File,.,PDV,1002,NONSMOKE,BLACK,54,Implicit output,1 0 0 2 N O N S M O K E R B L A C K 5 4,22,22,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 100

20、6 NONSMOKER BLACK 38,Raw Data File,.,PDV,1002,NONSMOKE,BLACK,54,Implicit return,1 0 0 2 N O N S M O K E R B L A C K 5 4,23,23,data demog; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 100

21、5 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,Raw Data File,.,PDV,1002,NONSMOKE,BLACK,54,1 0 0 2 N O N S M O K E R B L A C K 5 4,Continue processing until end of the raw data file.,24,24,PROC PRINT Output,proc print data=demog noobs; run;,subjid smoke race age 1001 EXSMOKER ASIAN 30 1002 NONSMOKE BLA

22、CK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKE WHITE 51 1006 NONSMOKE BLACK 38,UNEXPECTED VALUE,25,25,Length Statement,Using length statement to define the length of character variables Syntax,LENGTH variable length .;,26,26,Length Statement,Example PROC PRINT Output,subjid smoke

23、race age 1001 EXSMOKER ASIAN 30 1002 NONSMOKER BLACK 54 1003 EXSMOKER BLACK 45 1004 EXSMOKER WHITE 55 1005 NONSMOKER WHITE 51 1006 NONSMOKER BLACK 38,data demog; length subjid $5 smoke $10 race $10; infile C:ABCraw_data.txt; input subjid $ smoke $ race $ age; run;,27,27,Infile Statement,Identifies a

24、n external file to read with an INPUT statement Syntax,INFILE file-specification ;,28,28,Infile Statement Cont.,file-specification external-file : specifies the physical name of an external file fileref : specifies the fileref of an external file. fileref(file) CARDS | CARDS4|Datalines|Datalines4,da

25、ta demog; infile C:ABCraw_data.txt; input subjid $; run;,filename rawdata C:ABCraw_data.txt; data demog; infile rawdata; input subjid $; run;,29,29,Infile Statement Cont.,Options DELIMITER= quoted string specifies an alternate delimiter (other than a blank) to be used for LIST input quoted string :s

26、pecifies one or more characters to read as delimiters.,data demog; length subjid $5 dose $8; infile C:ABCraw_data2.txt delimiter=,; input subjid $ dosedate: date9. dose $; run;,1001 ,01JAN2010,10MG 1002, 05FEB2010, 10MG 1003,19DEC2009,10MG 1004,25JAN2010, 10MG 1005,31DEC2009,10MG 1006, 15JAN2010,10M

27、G,Raw data (raw_data2.txt),Data Step,30,30,Infile Statement Cont.,DSD When you specify DSD, SAS treats two consecutive delimiters as a missing value Missover prevents an INPUT statement from reading a new input data record if it does not find values in the current input line for all the variables in

28、 the statement. When an INPUT statement reaches the end of the current input data record, variables without any values assigned are set to missing . Example3,31,31,INFILE Statement Options,These options can be used separately or together in the INFILE statement.,32,32,Modified list input,Syntax,INPU

29、T variable : informat;,Examples of informats are COMMAw.reads numeric data ($4,242) and strips out selected nonnumeric characters, such as dollar signs and commas. MMDDYYw.reads dates in the form 12/31/2012. DATEw.reads dates in the form 29Feb2000. TIMEw. reads dates in the form 11:30.,33,33,Example

30、,1001 10/30/2009 10:00 A 1002 11/11/2009 10:01 B 1003 10/25/2009 10:00 B 1004 12/17/2009 10:03 B 1005 12/01/2009 10:05 A 1006 10/06/2009 10:01 A,Raw Data (raw_data4.txt),data demog; infile C:ABCraw_data4.txt ; input subjid $ dosedate: MMDDYY10. dosetime: time5. drugname $; run;,Data Step,34,34,34,Co

31、ntent,Start with creating a SAS Data Set Using list input to read delimited raw data files Other Input Styles Input raw data in SAS Reading Excel Spreadsheets Variable Attributes,35,35,Using Column Input to read fixed raw data files,Column input is appropriate for reading data in fixed columns Synta

32、x,INPUT variable start-column ;,start-column :specifies the first column of the input record that contains the value to read. - end-column :specifies the last column of the input record that contains the value to read.,36,36,Example,Raw Data (raw_data2.txt),data demog; infile C:ABCraw_data.txt; inpu

33、t subjid $ 1-4 smoke $ 5-14 race $15-20 age 21-23; run;,Data Step,37,37,Syntax for the other input style,Formatted input Named input,INPUT variable informat. ;,INPUT variable= ;,38,38,38,Content,Start with creating a SAS Data Set Using list input to read delimited raw data files Other Input Styles I

34、nput raw data in SAS Reading Excel Spreadsheets Variable Attributes,39,39,DATALINES statement,The last statement in the DATA step Immediately precedes the first data line. Use a null statement (a single semicolon) to indicate the end of the input data. Syntax,Datalines;,40,40,Example,data demog; inf

35、ile datalines delimiter=,; input subjid $ smoke $ race $ age; datalines; 1001,EXSMOKER,ASIAN,30 1002,NONSMOKER,BLACK,54 1003,EXSMOKER,BLACK,45 1004,EXSMOKER,WHITE,55 1005,NONSMOKER,WHITE,51 1006,NONSMOKER,BLACK,38 ; run;,With the INFILE statement to read in-stream data lines, you can use list input

36、to read data values that are delimited by commas instead of blanks.,41,41,CDARS statement,Is same to the Datalines statement. Syntax,CARDS;,42,42,CARDS4/ DATALINES4 statement,Use the CDARS/DATALINES4 statement when data contain semicolons. Following the data lines, four consecutive semicolons should

37、 be located in columns 1 through 4. Example,data demog; input subjid $ smoke $ race $ age; datalines4; 1001 EXSMOKER; ASIAN 30 1002 NONSMOKER; BLACK 54 1003 EXSMOKER; BLACK 45 1004 EXSMOKER; WHITE 55 1005 NONSMOKER; WHITE 51 1006 NONSMOKER; BLACK 38 ; run;,43,43,43,Content,Start with creating a SAS

38、Data Set Using list input to read delimited raw data files Other Input Styles Input raw data in SAS Reading Excel Spreadsheets Exercise,44,44,Create a SAS data set from an Excel spreadsheet using PROC IMPORT,Syntax Example,PROC IMPORT OUT= SAS-data-set DATAFILE= external-file-name DBMS=EXCEL2000 ; G

39、ETNAMES=YES; RUN;,PROC IMPORT OUT= random DATAFILE= c:ABCtest.xls DBMS=EXCEL2000 REPLACE; RANGE=sheet1.$; GETNAMES=YES; RUN;,45,45,45,Content,Start with creating a SAS Data Set Using list input to read delimited raw data files Other Input Styles Input raw data in SAS Reading Excel Spreadsheets,Exercise,46,46,Exercise,We collected a series of data as follows, We need to reading them in a dataset and save it in a permanent library WORKLOAD.,Subjid Date Time Drugname Dose Unit 10011001 10/