Getting the Data into SAS

GETTING DATA INTO SAS

direct link:http://www.biostat.ucla.edu/course/m403b/M403B2005_2DataInput.pdf

1. Importing data from other sources
• Use DBMS Copy, STAT Transfer, or some other software that performs similar functions.
• Use SAS/ACCESS or the SAS Import feature in Windows.
• Use PROC IMPORTS to read certain types of data files.
• Create a raw data (ASCII) file then use INPUT and INFILE statements to read the raw data file in SAS programs.

2. Creating SAS data sets with raw (ASCII) data files
• Data are placed within the SAS program directly after the DATALINES or CARDS statement. Use INPUT statement to read the data.

*** Read data within SAS program as SAS data file ***;

DATA survey;
INPUT Q1Major Q2Degree Q3SASpast Q4ProjData Q5SASexprn Q6SASfutur;
DATALINES;
2 1 1 1 2 8
2 2 0 0 0 7
2 1 0 0 1 4
2 1 0 1 0 8
more raw data lines
;
RUN;

• In the above example,
– Spaces (or other types of delimiters) are required between data values
– Missing values must be specified (blank spaces are treated as separators)
– Character data values cannot have embedded spaces (e.g., names and addresses)
– Cannot skip unwanted data column when reading in data

Getting Data Into SAS
• Data are placed in an external ASCII file. Use INFILE statement to identify the location of the external file and Use INPUT statement to read the data.

*** Read text (ASCII) data file as SAS data file ***;
DATA TXT_Surv;
INFILE "C:\Class\M403B\M403B2005Lab1Surv.TXT" missover;
INPUT @1 Q1Major 3.
@6 Q1Spec $char10.
@17 Q2Degree 3.
@22 Q2Spec $char10.
@33 Q3SASpast 2.
@36 Q4ProjData 2.
@39 Q5SASexprn 3.
@45 Q6SASfutur 3.
;
RUN;
Or,

*** Read text (ASCII) data file as SAS data file ***;
DATA TXT_Surv;
INFILE "C:\Class\M403B\M403B2005Lab1Surv.TXT" missover;
INPUT Q1Major 1-3
Q1Spec $ 6-15
Q2Degree 17-20
Q2Spec $ 22-31
Q3SASpast 33-34
Q4ProjData 36-37
Q5SASexprn 39-41
Q6SASfutur 45-47
;
RUN;

• In the above two examples,
– Spaces (or other types of delimiters) are not required between data values
– Missing values can be left blank
– Character data values can have embedded spaces (e.g., names and addresses)
– Can skip unwanted data columns when reading data

Getting Data Into SAS
• Check the SAS log to make sure the data are read correctly into SAS (e.g., number of observations, length of each record, and number of variables, etc.).

• If the length of records is very long (> 256 characters), use option LRECL=XXX in the INFILE statement, where XXX is the length of the longest record in the ASCII data file.

• If one or more records have unassigned (blank) missing values at the end of the record, use option MISSOVER in the INFILE statement to prevent SAS from going to the next record prematurely. MISSOVER tells SAS to assign missing values to these variables before going to the next record.

• If each record has multiple lines, in the INPUT statement, use a forward slash (/) after variable names in each line, or use #2 before variable names on the second line (and #3 for the third line, etc.). Note: the same number of lines per record is required, and use blank line(s) if some records do not have data in the corresponding data line(s).

*** Read multiple lines per record ***;
DATA twoliner;
INFILE ‘C:\rawdata\twolines.dat’;
INPUT id 1-8
sex 9-16
race 25-32 /
occ1 1-8
occ2 9-16
income 17-24
;
RUN;
Or,

*** Read multiple lines per record ***;
DATA twoliner;
INFILE ‘C:\rawdata\twolines.dat’;
INPUT #1 id 1-8
sex 17-24
race 25-32
#2 occ1 1-8
occ2 9-16
income 17-24
;
RUN;

Getting Data Into SAS
• If ASCII file contains variable names or other information at the beginning of the file, use option FIRSTOBS=X in the INFILE statement to skip the first X line(s).

• The default delimiter in SAS is a blank space (‘ ‘). If other type of delimiter, such as comma (‘,’) is used to separate data values, need to specify option DLM=’,’ (or DELIMITER=’,’) in the INFILE statement. Use option DSD if consecutive delimiters indicate missing values in between. Note: for tab delimited data, use option EXPANDTABS in the INFILE statement.

• If some columns of data values are not need, skip those columns when reading the raw data file. For example, the following program reads variables ID, OCC1, and INCOME, while other variables are not included in the output SAS data.

*** Read multiple lines per record and skip unwanted variables ***;
DATA twoliner;
INFILE ‘C:\rawdata\twolines.dat’;
INPUT #1 id 1-8
#2 occ1 1-8
income 17-24
;
RUN;

Getting Data Into SAS :

*** Define LIBREF MySASlib as a SAS library to store permanent SAS data sets ***;
LIBNAME MySASlib "C:\Class\M403B";

*** Create a permanent SAS data set Survey in LIBREF MySASlib in data step ***;
DATA MySASlib.Survey;
INFILE "C:\Class\M403B\M403B2005Lab1Surv.TXT" missover;
INPUT @1 Q1Major 3.
@6 Q1Spec $char10.
More input variables
;
RUN;

– MySASlib is the LIBREF, which refers to a subdirectory "C:\Class\M403B", and Survey is the name of the SAS data set, which can be found in the above subdirectory. Think of LIBREFs as pointers, where LIBREFs point to the location where permanent SAS data sets are located.

– More than one LIBNAME statements can be specified in one SAS program, if two or more permanent SAS data sets are created or used in different subdirectories.
• Identifying permanent SAS data sets

– Identify the permanent SAS data set by using an appropriate LIBREF, defined with a LIBNAME statement previously.

*** Examine the contents of a permanent SAS data set Survey located in LIBREF MySASlib using PROC CONTENTS ***;

PROC CONTENTS DATA=MySASlib.Survey Position;
RUN;

– The PROC CONTENTS option POSITION produces output with variables listed in the order in which they appear in the data set (by position), as well as alphabetically (the default).

Getting Data Into SAS

4. Exporting data from SAS data sets
• Use DBMS Copy, STAT Transfer, or some other software that performs similar functions.

• Use SAS/ACCESS or the SAS Export feature in Windows.

• Use PROC EXPORTS to write certain types of data files.

• Create raw data (ASCII) files using PUT and FILE statements in SAS program.

*** Define LIBREF M403 as a SAS library to store permanent SAS data sets ***;
LIBNAME M403 "C:\Class\M403B";
*** Write a text (ASCII) data file from a SAS data set ***;
DATA _NULL_;
SET M403.Survey
FILE "C:\Class\M403B\M403B_SurveyData.TXT" ;
PUT @1 Q1Major 3.
@6 Q1Spec $char10.
@17 Q2Degree 3.
@22 Q2Spec $char10.
@33 Q3SASpast 2.
@36 Q4ProjData 2.
@39 Q5SASexprn 3.
@45 Q6SASfutur 3.
;
RUN;

– In the above example, an ASCII file M403B_SurveyData.TXT is created and stored in a subdirectory "C:\Class\M403B" using a permanent SAS data set Survey stored in a SAS library referred by M403, which points to the subdirectory "C:\Class\M403B".

– If column numbers had not been specified, SAS would place a space between each variable value in the ASCII file.

– The SAS special data set _NULL_ is used because a SAS data set is not being created in this data step (_NULL_ tells SAS to not go to the trouble of building a SAS data set).

– It is not necessary to identify character variables with the $ sign because SAS already knows which variables are character variables.

Getting Data Into SAS
5. Transporting SAS data sets from one type of host (e.g., Unix) to another (e.g., Windows)

• In SAS Version 6 and lower, if a SAS data set is created in one type of host (e.g. Unix) and is used in another (e.g. Windows), a SAS transport file needs to be created first. This is not necessary in SAS Version 8 or higher. Using PROC COPY, the following example creates a SAS transport file called SAS_Surv.XPT from the SAS data set Survey located in the C:\Class\M403B subdirectory:

*** Define LIBREF M403 as a SAS library to store permanent SAS data sets ***;
LIBNAME M403 "C:\Class\M403B";

*** Define LIBREF XPT as a SAS transport file to be used in different host systems ***;
LIBNAME XPT XPORT "C:\Class\M403B\SAS_Surv.XPT";

*** Define LIBREF XPT as a SAS transport file to be used in different host systems ***;
PROC COPY IN=M403 OUT=XPT;
SELECT Survey;
RUN;

– XPORT is a SAS keyword that tells SAS to create a transport file.

– M403 is the LIBREF pointing to the location of the SAS data set; and XPT is the LIBREF referring to the SAS transport file.

– IN, OUT, and SELECT are SAS keywords specifying the input data set location (SAS data set), output data file (SAS transport file), and the name of the SAS data set from which the transport data set will be made.

• After the SAS transport file is created, SAS data set(s) contained in the transport file can be accessed directly in the "new" host. The following example creates a SAS dataset Survey from a SAS transport file SAS_Surv.XPT, which is located in C:\Class\M403B.

PROC COPY IN=XPT OUT=M403;
RUN;
DATA M403.Survey;
SET XPT.Survey;
RUN;