In order to create reports with SAS procedures, your data must be in the form of a SAS data set. If your data is not stored in the form of a SAS data set, you need to create a SAS data set by entering data, by reading raw data, or by accessing files that were created by other software.

This chapter shows you how to design and write DATA step programs to create SAS data sets from raw data that is stored in an external file and from data stored in Microsoft Excel work-sheets. It also shows you how to read data from a SAS data set and write observations out to these sources.

Regardless of the input data source — raw files or Excel work–sheets — you use the DATA step to read in the data and create the SAS data set.

In this chapter, you learn to

As you begin to write the program, remember that you might need to use a LIBNAME statement to reference the permanent SAS library in which the data set will be stored.

For example, the LIBNAME statement below assigns the libref Taxes to the SAS library C:AcctQtr1Report in the Windows environment.

libname taxes 'c:acctqtr1
eport';

You do not need to use a LIBNAME statement in all situations. For example, if you are storing the data set in a temporary SAS data set or if SAS has automatically assigned the libref for the permanent library that you are using.

Before you can read your raw data, you must point to the location of the external file that contains the data. You use the FILENAME statement to point to this location.

Just as you assign a libref by using a LIBNAME statement, you assign a fileref by using a FILENAME statement.

Filerefs perform the same function as librefs: they temporarily point to a storage location for data. However, librefs reference SAS data libraries, whereas filerefs reference external files.

The following FILENAME statement temporarily associates the fileref Tests with the external file that contains the data from the exercise stress tests. The complete filename is specified as C:UsersTmill.dat in the Windows environment.

filename tests 'c:users	mill.dat';

You can also use a FILENAME statement to associate a fileref with an aggregate storage location, such as a directory that contains multiple external files.

This FILENAME statement temporarily associates the fileref Finance with the aggregate storage directory C:UsersPersonalFinances:

filename finance 'c:userspersonalfinances';

Like librefs, the filerefs currently defined for your SAS session are listed in the SAS Explorer window.

To view details about a referenced file, double-click File Shortcuts (or select File Shortcuts and then Open from the pop-up menu). Then select View

When you associate a fileref with an individual external file, you specify the fileref in subsequent SAS statements and commands.

To reference an external file with a fileref that points to an aggregate storage location, you specify the fileref followed by the individual filename in parentheses:

infile tax('refund'),

For details on referencing external files stored in aggregate storage locations, see the SAS documentation for your operating environment.

The DATA statement indicates the beginning of the DATA step and names the SAS data set to be created.

data clinic.stress;

Remember that the SAS data set name is a two-level name. For example, the two-level name Clinic.Admit specifies that the data set Admit is stored in the permanent SAS library to which the libref Clinic has been assigned.

When reading raw data, use the INFILE statement to indicate which file the data is in.

data clinic.stress;

infile tests obs=10;

To read the raw data file to which the fileref Tests has been assigned, you write the following INFILE statement:

infile tests;

infile 'c:irspersonal
efund.dat';

In this chapter, you'll be working with column input, the most common input style. Column input specifies actual column locations for values. However, column input is appropriate only in certain situations. When you use column input, your data must be

Standard numeric data values can contain only

Nonstandard numeric data includes

The external file that is referenced by the fileref Staff contains the personnel information for a technical writing department of a small computer manufacturer. The fields contain values for each employee's last name, first name, job title, and annual salary.

Notice that the values for Salary contain commas. So, the values for Salary are considered to be nonstandard numeric values. You cannot use column input to read these values.

Raw data can be organized in several different ways.

This external file contains data that is free-format, meaning data that is not arranged in columns. Notice that the values for a particular field do not begin and end in the same columns. You cannot use column input to read this file.
This external file contains data that is arranged in columns or fixed fields. You can specify a beginning and ending column for each field. Let's look at how column input can be used to read this data.

The INPUT statement describes the fields of raw data to be read and placed into the SAS data set.

data clinic.stress;

infile tests obs=10;

run;

Look at the small data file shown below. For each field of raw data that you want to read into your SAS data set, you must specify the following information in the INPUT statement:

The INPUT statement below assigns the character variable ID to the data in columns 1-4, the numeric variable Age to the data in columns 6-7, the character variable ActLevel to the data in columns 9-12, and the character variable Sex to the data in column 14.

When you use column input, you can

input ActLevel $ 9-12 Sex $ 14 Age 6-7;

Each variable has a name that conforms to SAS naming conventions. Variable names

Let's look at an INPUT statement that uses column input to read the three data fields in the raw data file below.

The values for the variable that you are naming Age are located in columns 1-2. Because Age is a numeric variable, you do not specify a dollar sign ($) after the variable name.

input Age 1-2

The values for the variable ActLevel are located in columns 3-6. You specify a $ to indicate that ActLevel is a character variable.

input Age 1-2  ActLevel $ 3-6

The values for the character variable Sex are located in column 7. Notice that you specify only a single column.

input Age 1-2 ActLevel $ 3-6  Sex $ 7;

To verify your data, it is a good idea to use the OBS= option in the INFILE statement. Adding OBS=n to the INFILE statement enables you to process only records 1 through n, so you can verify that the correct fields are being read before reading the entire data file.

The program below reads the first ten records in the raw data file referenced by the fileref Tests. The data is stored in a permanent SAS data set, named Sasuser.Stress. Don't forget a RUN statement, which tells SAS to execute the previous SAS statements.

data sasuser.stress;
   infile tests  obs=10;
   input ID 1-4 Name $ 6-25
         RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40
         TimeSec 42-43 Tolerance $ 45;
run;

If you submitted the DATA step below in the previous guided practice, it ran successfully.

data sasuser.stress;
   infile tests obs=10;
   input ID 1-4 Name $ 6-25
         RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40
         TimeSec 42-43 Tolerance $ 45;
run;

Messages in the log verified that the raw data file was read correctly. The notes in the log indicate that

The messages in the log seem to indicate that the DATA step program correctly accessed the raw data file. But it is a good idea to look at the ten observations in the new data set before reading the entire raw data file. You can submit a PROC PRINT step to view the data.

data clinic.stress;

infile tests obs=10;

run;

run;

The following PROC PRINT step lists the Sasuser.Stress data set.

proc print data=sasuser.stress;
run;

The PROC PRINT output indicates that the variables in the Sasuser.Stress data set were read correctly for the first ten records.

Now that you've checked the log and verified your data, you can modify the DATA step to read the entire raw data file. To do so, remove the OBS= option from the INFILE statement and re-submit the program.

data sasuser.stress;
   infile tests;
   input ID 1-4 Name $ 6-25
         RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40
         TimeSec 42-43 Tolerance $ 45;
run;

When you submit the revised DATA step and check the log, you see a note indicating that invalid data appears for the variable RecHR in line 14 of the raw data file, columns 35-37.

This note is followed by a column ruler and the actual data line that contains the invalid value for RecHR.

The value Q13 is a data-entry error. It was entered incorrectly for the variable RecHR.

RecHR is a numeric variable, but Q13 is not a valid number. So RecHR is assigned a missing value, as indicated in the log. Because RecHR is numeric, the missing value is represented with a period.

Notice, though, that the DATA step does not fail as a result of the invalid data but continues to execute. Unlike syntax errors, invalid data errors do not cause SAS to stop processing a program.

Assuming that you have a way to edit the file and can justify a correction, you can correct the invalid value and rerun the DATA step. If you did this, the log would then show that the data set Sasuser.Stress was created with 21 observations, 8 variables, and no messages about invalid data.

After correcting the raw data file, you can list the data again to verify that it is correct.

proc print data=sasuser.stress;
run;

Whenever you use the DATA step to read raw data, remember the steps that you followed in this chapter, which help ensure that you don't waste resources when accessing data:

You use SAS expressions in assignment statements and many other SAS programming statements to

An expression is a sequence of operands and operators that form a set of instructions. The instructions are performed to produce a new value:

To perform a calculation, you use arithmetic operators. The table below lists arithmetic operators.

When you use more than one arithmetic operator in an expression,

You use the following comparison operators to express a condition.

To link a sequence of expressions into compound expressions, you use logical operators, including the following:

The assignment statement in the DATA step below creates a new variable, TotalTime, by multiplying the values of TimeMin by 60 and then adding the values of TimeSec.

data sasuser.stress;
   infile tests;
   input ID 1-4 Name $ 6-25 RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40 TimeSec 42-43
         Tolerance $ 45;
    TotalTime=(timemin*60)+timesec;
run;

The expression can also contain the variable name that is on the left side of the equal sign, as the following assignment statement shows. This statement re-defines the values of the variable RestHR as 10 percent higher.

data sasuser.stress;
   infile tests;
   input ID 1-4 Name $ 6-25 RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40 TimeSec 42-43
         Tolerance $ 45;
   resthr=resthr+(resthr*.10);
run;

When a variable name appears on both sides of the equal sign, the original value on the right side is used to evaluate the expression. The result is assigned to the variable on the left side of the equal sign.

data sasuser.stress;
   infile tests;
   input ID 1-4 Name $ 6-25 RestHR 27-29 MaxHR 31-33
           RecHR 35-37 TimeMin 39-40 TimeSec 42-43
           Tolerance $ 45;
   resthr=resthr+(resthr*.10);
run;   ^         ^
  result       original value

You can assign date values to variables in assignment statements by using date constants. To represent a constant in SAS date form, specify the date as 'ddmmmyy' or 'ddmmmyyyy' , followed by a D.

General form, date constant:

   'ddmmmyy'd

or

   "ddmmmyy"d

where

• dd is a one- or two-digit value for the day

• mmm is a three-letter abbreviation for the month (JAN, FEB, and so on)

• yy or yyyy is a two- or four-digit value for the year, respectively.

In the following program, the second assignment statement assigns a date value to the variable TestDate.

data sasuser.stress;
   infile tests;
   input ID 1-4 Name $ 6-25 RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40 TimeSec 42-43
         Tolerance $ 45;

   TotalTime=(timemin*60)+timesec;
   TestDate='01jan2000'd;
run;

Time='9:25't;
DateTime='18jan2005:9:27:05'dt;

The subsetting IF statement below selects only observations whose values for Tolerance are D. It is positioned in the DATA step so that other statements do not need to process unwanted observations.

data sasuser.stress;
   infile tests;
   input ID 1-4 Name $ 6-25 RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40 TimeSec 42-43
         Tolerance $ 45;
   if tolerance='D';
   TotalTime=(timemin*60)+timesec;
run;

Because Tolerance is a character variable, the value D must be enclosed in quotation marks, and it must be the same case as in the data set.

To read the data for the treadmill stress tests as instream data, you can submit the following program:

data sasuser.stress;
   input ID 1-4 Name $ 6-25 RestHR 27-29 MaxHR 31-33
         RecHR 35-37 TimeMin 39-40 TimeSec 42-43
         Tolerance $ 45;
   if tolerance='D';
   TotalTime=(timemin*60)+timesec;
   datalines; 
2458 Murray, W            72  185 128 12 38 D 
2462 Almers, C            68  171 133 10  5 I 
2501 Bonaventure, T       78  177 139 11 13 I 
2523 Johnson, R           69  162 114  9 42 S 
2539 LaMance, K           75  168 141 11 46 D 
2544 Jones, M             79  187 136 12 26 N 
2552 Reberson, P          69  158 139 15 41 D 
2555 King, E              70  167 122 13 13 I 
2563 Pitts, D             71  159 116 10 22 S 
2568 Eberhardt, S         72  182 122 16 49 N 
2571 Nunnelly, A          65  181 141 15  2 I 
2572 Oberon, M            74  177 138 12 11 D 
2574 Peterson, V          80  164 137 14  9 D 
2575 Quigley, M           74  152 113 11 26 I 
2578 Cameron, L           75  158 108 14 27 I 
2579 Underwood, K         72  165 127 13 19 S 
2584 Takahashi, Y         76  163 135 16  7 D 
2586 Derber, B            68  176 119 17 35 N 
2588 Ivan, H              70  182 126 15 41 N 
2589 Wilcox, E            78  189 138 14 57 I 
2595 Warren, C            77  170 136 12 10 S 
;

Because the goal of your SAS program is to create a raw data file and not a SAS data set, it is inefficient to list a data set name in the DATA statement. Instead, use the keyword _NULL_, which enables you to use the DATA step without actually creating a SAS data set. A SET statement specifies the SAS data set that you want to read from.

data _null_;
    set sasuser.stress;

The next step is to specify the output file.

You use the FILE and PUT statements to write the observations from a SAS data set to a raw data file, just as you used the INFILE and INPUT statements to create a SAS data set. These two sets of statements work almost identically.

When writing observations to a raw data file, use the FILE statement to specify the output file.

For example, if you want to read the Sasuser.Stress data set and write it to a raw data file that is referenced by the fileref Newdat, you would begin your program with the following SAS statements.

data _null_;
   set sasuser.stress;
   file newdat;

Instead of identifying the raw data file with a SAS fileref, you can choose to specify the entire filename and location in the FILE statement. For example, the following FILE statement points directly to the C:ClinicPatientsStress.dat file. Note that the path specifying the filename and location must be enclosed in quotation marks.

data _null_;
   set sasuser.stress;
   file 'c:clinicpatientsstress.dat';

Whereas the FILE statement specifies the output file, the PUT statement describes the lines to write to the raw data file.

In general, the PUT statement mirrors the capabilities of the INPUT statement. In this case you are working with column output. Therefore, you need to specify the variable name, starting column, and ending column for each field that you want to create. Because you are creating raw data, you don't need to follow character variable names with a dollar sign ($).

data _null_;
   set sasuser.stress;
   file 'c:clinicpatientsstress.dat';
   put id 1-4 name   6-25 resthr 27-29 maxhr 31-33 
        rechr 35-37 timemin 39-40 timesec 42-43 
        tolerance 45 totaltime 47-49;
run;

The resulting raw data file would look like this.

In later chapters you'll learn how to use INPUT and PUT statements to read and write raw data in other forms and record types.

In addition to being able to read raw data files, SAS can also read Microsoft Excel data. Whether the input data source is a SAS data set, a raw data file, or a file from another application, you use the DATA step to create a SAS data set. The difference between reading these various types of input is in how you reference the data. To read in Excel data you use one of the following methods:

Remember, the Base SAS LIBNAME statement associates a SAS name (libref) with a SAS DATA library by pointing to its physical location. But, the SAS/ACCESS LIBNAME statement associates a SAS name with an Excel workbook file by pointing to its location.

In doing so, the Excel workbook becomes a new library in SAS, and the work-sheets in the workbook become the individual SAS data sets in that library.

The figure below illustrates the difference between how the two LIBNAME statements treat the data.

The next figure shows how the DATA step is used with three types of input data.

Notice how the INFILE and INPUT statements are used in the DATA step for reading raw data, but the SET statement is used in the DATA step for reading in the Excel work-sheets.

Let's take a look at the steps for reading in an Excel workbook file.

To read the Excel workbook file, the DATA step must provide the following instructions to SAS:

The table below outlines the basic statements that are used in a program that reads Excel data and creates a SAS data set from an Excel work-sheet. The PROC CONTENTS and PROC PRINT statements are not requirements for reading in Excel data and creating a SAS data set. However, these statements are useful for confirming that your Excel data has successfully been read into SAS.

'c:usersexercise.xlsx';

The general form of the SAS/ACCESS LIBNAME statement is as follows:

This example uses data similar to the scenario used for the raw data in the previous section. The data shows the readings from exercise stress tests that have been performed on patients at a health clinic.

The stress test data is located in an Excel workbook named exercise.xlsx (shown below), which is stored in the location c:users.

Notice in the sample work-sheet above that the date column is defined in Excel as dates. That is, if you right click on the cells and select Format Cells (in Excel), the cells have a category of Date. SAS reads this data just as it is stored in Excel. If the date had been stored as text in Excel, then SAS would have read it as a character string.

To read in this workbook, you must first create a libref to point to the workbook's location:

libname results 'c:usersexercise.xlsx';

The LIBNAME statement creates the libref results, which points to the Excel workbook exercise.xlsx. The workbook contains two work-sheets, tests and ActLevel, which are now stored in the new SAS library (results) as data sets.

After submitting the LIBNAME statement, you can look in the SAS Explorer window to see how SAS handles your Excel workbook. The Explorer window enables you to manage your files in the SAS windowing environment.

Name Literals

In the figure above, notice how the LIBNAME statement created a permanent library, results, which is the SAS name (libref) we gave to the workbook file and its location. The new library contains two SAS data sets, which contain the data from the Excel worksheets. From this window you can browse the list of SAS libraries or display the descriptor portion of a SAS data set.

Notice that the Excel work-sheet names have the special character ($) at the end. All Excel worksheets are designated this way. But remember, special characters such as these are not allowed in SAS data set names by default. So, in order for SAS to allow this character to be included in the data set name, it assigns a name literal to the data set name. A SAS name literal is a name token that is expressed as a string within quotation marks, followed by the uppercase or lowercase letter n. The name literal tells SAS to allow the special character ($) in the data set name.

Named Ranges

A named range is a range of cells within a work-sheet that you define in Excel and assign a name to. In the example below, the worksheet contains a named range, tests_week_1, which SAS recognizes as a data set.

The named range, tests_week_1, and its parent work-sheet, tests, will appear in the SAS Explorer window as separate data sets with the same name, except that the data set created from the named range will have no dollar sign ($) appended to its name.

For more information on named ranges, see your Microsoft Excel documentation.

In addition to using the SAS explorer window to view library data, you can also use the CONTENTS procedure with the _ALL_ keyword to produce information about a data library and its contents. In the example below, PROC CONTENTS outputs summary information for the SAS data set tests, including data set name, variables, data types, and other summary information. This statement is useful for making sure that SAS is successfully reading in your Excel data before moving on to the DATA step.

proc contents data=results._all_;
run;

About the sample output above:

You use the DATA statement to indicate the beginning of the DATA step and name the SAS data set to be created. Remember that the SAS data set name is a two-level name. For example, the two-level name results.Admit specifies that the data set Admit is stored in the permanent SAS library to which the libref results has been assigned.

When reading Excel data, use the STEP statement to indicate which work-sheet in the data set you want to read. To read in the Excel file you write the DATA and SET statements as follows:

data work.stress;
   set results.'ActLevel$'n;
run;

In this example, the DATA statement tells SAS to name the new data set, stress, and store it in the temporary library WORK. The SET statement in the DATA step specifies the libref (reference to the Excel file) and the work-sheet name as the input data.

You can use several statements in the DATA step to subset your data as needed. Here, the WHERE statement is used with a variable to include only those participants whose activity level is HIGH.

data work.stress;
   set results.'ActLevel$'n;

   where ActLevel='HIGH';
run;

The figure below shows the partial output for this DATA step in table format.

After using the DATA step to read in the Excel data and create the SAS data set, you can use PROC PRINT to produce a list report that displays the data set values.

You can also use the PRINT procedure to refer to a specific work-sheet. Remember to use the name literal when referring to a specific Excel work-sheet. In the example below, the first PRINT statement displays the data values for the new data set that was created in the DATA step. The second PRINT statement displays the contents of the Excel work-sheet that was created by the LIBNAME statement.

proc print data=work.stress;
run;
proc print data=results.'ActLevel$'n;
run;

As an alternative to using programming statements, you can use the Import Wizard to guide you through the process of creating a SAS data set from both raw data and from Excel work-sheets. The Import Wizard enables you to create a SAS data set from different types of external files, such as

To access the Import Wizard, select File

Follow the instructions on each screen of the Import Wizard to read in your data. If you need additional information, select the Help button at the bottom of each screen in the wizard.

Just as you can create a SAS data set from raw data by using the Import Wizard, you can use the Export Wizard to read data from a SAS data set and to write the data to an external data source. To access the Export Wizard, select File

As an alternative to using programming statements, you can use the Import Wizard to guide you through the process of creating a SAS data set from raw data. The Import Wizard enables you to create a SAS data set from different types of external files, such as

To access the Import Wizard, select Tools

Follow the instructions on each screen of the Import Wizard to read in your data. As an alternative to using programming statements, you can use the Import Data task to guide you through the process of creating a SAS data set from raw data. The Import Data task enables you to create a SAS data set from different types of external files, such as

To access the Import Data window, select Tools

Follow the instructions on each screen of the Import Data window to read in your data. As an alternative to using programming statements, you can use the Import Data task to guide you through the process of creating a SAS data set from external files. The Import Data task enables you to create a SAS data set from different types of external files, such as

To access the Import Data window, select File

Follow the instructions on each screen of the Import Data window to read in your data.