Example 20.1

The following program uses column input to read in the values of one character variable (name) and four numeric variables (subj, gender, height, and weight) into a temporary SAS data set called temp:

DATA temp;
input subj 1-4 name $ 6-23 gender 25 height 27-28 weight 30-32;
CARDS;
1024 Alice Smith        1 65 125
1167 Maryann White      1 68 140
1168 Thomas Jones       2 68 190
1201 Benedictine Arnold 2 68 190
1302 Felicia Ho         1 63 115
	;
RUN;
 
PROC PRINT data=temp;
	title 'Output dataset: TEMP';
RUN;

Output

Launch and run   the SAS program, and review the output from the PRINT procedure to convince yourself that the data are read in properly.

Here are the important points to recall about column input:

• When using column input, you are not required to indicate missing values with a placeholder, such as a period. That is, missing values can be left blank.
• Column input uses the columns specified to determine the length of character variables, thereby allowing character values to exceed the default 8 characters and to have embedded spaces.
• Column input allows fields to be skipped altogether or to be read in any order.
• Column input allows only part of a value to be read and allows values to be re-read.
• Spaces are not required between the data values.

Now, let's investigate how column input can be modified to accommodate reading in values that contain special characters.

Example 20.2

The following program uses formatted input to read two standard numeric variables (subj and height), two standard character variables (f_name and l_name), and two nonstandard numeric variables (wt_date and calorie) into a temporary SAS data set called temp:

DATA temp;
  input @1  subj 4. 
		@6  f_name $11. 
		@18 l_name $6.
		+3 height 2. 
		+5 wt_date mmddyy8. 
		+1 calorie comma5.;
	format wt_date mmddyy8. calorie comma5.;
	DATALINES;
1024 Alice       Smith  1 65 125 12/1/95  2,036
1167 Maryann     White  1 68 140 12/01/95 1,800
1168 Thomas      Jones  2    190 12/2/95  2,302
1201 Benedictine Arnold 2 68 190 11/30/95 2,432
1302 Felicia     Ho     1 63 115 1/1/96   1,972
			;
		RUN;
 
PROC PRINT data = temp;
	title 'Output dataset: TEMP';
	id subj;
RUN;

Output

Recall that the @n absolute pointer control tells SAS to move the input pointer to a specific column number n. For example, the INPUT statement tells SAS to move the input pointer to column 18 before starting to read the values for the l_name variable. Recall that the +n relative pointer control tells SAS to move the input pointer forward n columns to a column number that is relative to the current position. For example, the INPUT statement tells SAS, after reading the values for the l_name variable, to move the input pointer 3 positions to the right before starting to read the values for the height variable.

The numeric informat 4. that follows subj in the INPUT statement tells SAS that the values for the numeric variable subj occupy four columns. The character informat $11. that follows f_name in the INPUT statement tells SAS that the values for the character variable f_name occupy eleven columns. And, the special informats mmddyy8. and comma5. that follow wt_date and calorie, respectively, in the INPUT statement tell SAS what to expect when reading in the values for the numeric variables wt_date and calorie.

Launch and run   the SAS program, and review the output from the PRINT procedure to convince yourself that the data are read in properly. Oh ... you might want to also recall that the PRINT procedure displays the values for wt_date and calorie in a meaningful way only because of the presence of the FORMAT statement in the DATA step. If this doesn't sound familiar, you might want to remove the FORMAT statement and re-run the program to see the less than helpful values that are displayed for the wt_date variable.

Example 20.3

The following program uses list input to read in the subject number, name, gender, weight, and height of five individuals into a temporary SAS data set called temp:

DATA temp;
  input subj name $ gender height weight;
  CARDS;
  1024 Alice 1 65 125
  1167 Maryann 1 68 140
  1168 Thomas 2 68 190
  1201 Benedictine . 68 190
  1302 Felicia 1 63 115
  ;
RUN;
 
PROC PRINT data=temp NOOBS;
title 'Output dataset: TEMP';
RUN;

Output

Launch and run   the SAS program, and review the output from the PRINT procedure to convince yourself that the data are read in properly.

Although list input might be the easiest input style to use, the ease comes with a bit of a price. In its default form, list input does have several limitations, including:

• Fields must be separated by at least one blank (or other delimiter).
• Fields must be read in order from left to right.
• You cannot skip or re-read fields.
• Missing values must be represented by a placeholder such as a period. (A blank field causes the matching of variable names and values to get out of sync.)
• Character values can't contain embedded blanks (or other delimiters).
• Although the width of a field can be greater than eight columns, both character and numeric variables have a default length of 8 bytes. Character values that are longer than eight characters are truncated.
• Data must be in standard character or numeric format.

Example 20.4

The following program is identical to the program in Example 20.2, except the PRINT procedure has been replaced with the CONTENTS procedure so that we can explore how SAS defines the lengths of variables:

DATA temp;
input @1  subj 4. 
		@6  f_name $11. 
		@18 l_name $6.
		+3 height 2. 
		+5 wt_date mmddyy8. 
		+1 calorie comma5.;
format wt_date mmddyy8. calorie comma5.;
DATALINES;
1024 Alice       Smith  1 65 125 12/1/95  2,036
1167 Maryann     White  1 68 140 12/01/95 1,800
1168 Thomas      Jones  2    190 12/2/95  2,302
1201 Benedictine Arnold 2 68 190 11/30/95 2,432
1302 Felicia     Ho     1 63 115 1/1/96   1,972
;
RUN;
 
PROC CONTENTS data = temp;
	title 'Contents of TEMP Data Set';
RUN;

Output

Launch and run   the SAS program, and when reviewing the output of the CONTENTS procedure, direct your attention to this portion:

Let's see what we can learn from this ... SAS defined the length of the character variables — f_name and l_name — as 11 and 6, respectively. Hmm ... is it just a coincidence that the informats we used in the INPUT statement for these two variables were $11.? and $6., respectively? Of course not! In general, during the compile phase, SAS defines the lengths of the character variables in the program data vector to be the same as the field width w specified by the $w. character informat.

Can the same generalization be made for the numeric variables, such as subj, height, wt_date, and calorie? Apparently not! For example, we used the numeric informat of 4. to read in the subj variable, and yet SAS assigned the subj variable a length of 8 bytes. Similarly, we used the comma5. informat to read in the calorie variable, and yet SAS assigned the calorie variable a length of, not 5, but 8 bytes. In general, by default, SAS stores a numeric value— no matter how many digits the value contains — as a floating-point number in 8 bytes of storage. That is, the length of a stored numeric variable is not affected by an informat's width nor by any other column specifications in an INPUT statement. Unfortunately, that doesn't mean you can be lazy and use a default width of 8 for all of your numeric informats. If you do, you'll quickly see that your data values are not read properly.

Example 20.5

The following program is similar to the program in the previous example, except all of the input pointer controls have been changed to @n absolute pointer controls and the numeric informat for the height variable has been changed from 2. to 8.:

DATA temp;
input @1  subj 4. 
		@6  f_name $11. 
		@18 l_name $6.
		@30 height 8. 
		@34 wt_date mmddyy8. 
		@43 calorie comma5.;
format wt_date mmddyy8. calorie comma5.;
DATALINES;
1024 Alice       Smith  1 65 125 12/1/95  2,036
1167 Maryann     White  1 68 140 12/01/95 1,800
1168 Thomas      Jones  2    190 12/2/95  2,302
1201 Benedictine Arnold 2 68 190 11/30/95 2,432
1302 Felicia     Ho     1 63 115 1/1/96   1,972
;
RUN;
 
PROC PRINT data = temp;
	title 'The TEMP Data Set';
RUN;

Output

Launch and run   the SAS program, and review the output from the PRINT procedure to convince yourself that SAS had trouble reading the data values for the height variable. If you take a look at the input data, you can see why. If you start in column 30, where the height values begin, and count eight columns to the right, you should see that SAS encounters two blank spaces — the one between the height and weight values and the one between the weight and date values. Because a blank space is a character value, and the height variable is defined as numeric, SAS gets confused, and displays an Invalid data message in the log window:

NOTE: Invalid data for height in line 299 30-37.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
           1024 Alice       Smith  1 65 125 12/1/95  2,036
      subj=1024 f_name=Alice l_name=Smith height=. wt_date=12/01/95 calorie=2,036 _ERROR_=1 _N_=1
NOTE: Invalid data for height in line 300 30-37.
          1167 Maryann     White  1 68 140 12/01/95 1,800
      subj=1167 f_name=Maryann l_name=White height=. wt_date=12/01/95 calorie=1,800 _ERROR_=1 _N_=2
NOTE: Invalid data for height in line 301 30-37.
          1168 Thomas      Jones  2    190 12/2/95  2,302
      subj=1168 f_name=Thomas l_name=Jones height=. wt_date=12/02/95 calorie=2,302 _ERROR_=1 _N_=3
NOTE: Invalid data for height in line 302 30-37.
          1201 Benedictine Arnold 2 68 190 11/30/95 2,432
      subj=1201 f_name=Benedictine l_name=Arnold height=. wt_date=11/30/95 calorie=2,432 _ERROR_=1 _N_=4
NOTE: Invalid data for height in line 303 30-37.
          1302 Felicia     Ho     1 63 115 1/1/96   1,972
      subj=1302 f_name=Felicia l_name=Ho height=. wt_date=01/01/96 calorie=1,972 _ERROR_=1 _N_=5
NOTE: The data set WORK.TEMP has 5 observations and 6 variables.
NOTE: DATA statement used (Total process time):
          real time           0.01 seconds
          cpu time            0.01 seconds

and assigns missing values to the height variable. Because we used @n absolute pointer controls to tell SAS where to begin reading the wt_date and calorie values, SAS has no trouble reading in the data for those variables.

In a nutshell, the moral of the story of the two previous examples is that you need to make sure that you define the widths of your character and numeric informats to be the number of columns the data values occupy in the input data, not the length of their resulting variables.

Example 20.6

The following program attempts to read the addresses.dat raw data file:

using formatted input:

DATA temp;
    infile 'C:\yourdrivename\Stat481WC\01importingI\sasndata\addresses.dat';
      input @1 subj 4. name $ 6-23 street $ 27-45;
RUN;
PROC PRINT;
    title 'Temp data set';
RUN;

Example 20.7

As you now know, by default, SAS goes to the next data line to read more data if SAS has reached the end of a data line and there are still more variables in the INPUT statement that have not been assigned values. The following program uses the INFILE statement's MISSOVER option to tell SAS not to advance to the next data line to read more values to complete observation, but rather to assign missing values to the remaining variables:

DATA temp;
    infile 'C:\yourdrivename\Stat481WC\01importingI\sasndata\addresses.dat' MISSOVER;
      input @1 subj 4. name $ 6-23 street $ 27-45;
RUN;
PROC PRINT;
    title 'Temp data set';
RUN;

If you haven't already done so, click on the link to the addresses.dat data file in order to save the file to a convenient location on your computer. And, upon launching the SAS program, edit the INFILE statement so it reflects the location in which you saved the file. Then, run   the SAS program, and review the output. You should see that our resulting data set is much improved:

At least this time, SAS associates the data values that it does read in with the correct variables. It's still not a perfect solution though, is it? In some cases, you might instead want SAS to read in the portions of the data fields that are available, such as 123 Elm St. for the street address for subj 1024, rather than automatically assigning a missing value when an end-of-record marker is encountered. That's where the INFILE statement's PAD option proves useful.

Example 20.8

The following program uses the INFILE statement's PAD option to tell SAS to pad each record in the data file with blanks so that all of the data lines have the same length:

DATA temp;
    infile 'C:\yourdrivename\Stat481WC\01importingI\sasndata\addresses.dat' PAD;
      input @1 subj 4. name $ 6-23 street $ 27-45;
RUN;
PROC PRINT;
    title 'Temp data set';
RUN;

Again, if you haven't already done so, click on the link to the addresses.dat data file in order to save the file to a convenient location on your computer. And, upon launching the SAS program, edit the INFILE statement so it reflects the location in which you saved the file. Then, run   the SAS program, and review the output. You should see that we have achieved the most complete and correct data set yet:

Example 20.9

The following program uses list input and the INFILE statement's MISSOVER option to read in the number of books five children read each week in a library's summer reading program when some of the values are missing at the end of a record:

DATA reading;
	infile DATALINES MISSOVER;
	input Name $ Week1-Week5;
	DATALINES;
Robin 3 2 5 1 6
Jack 4 4 4 3 4
Tim 3 0 0
Martin 1 0 1 1
Caroline 2 3 4 5 6
RUN;
 
PROC PRINT data = reading;
	title 'Summer Reading Program';
	var Name Week1-Week5;
RUN;

Output

First, review the data to note that Tim has no values recorded for the fourth and fifth week and Martin has no values recorded for the fifth week. Also, note that just so you don't have to go through the rigamarole of downloading a data file and editing the INFILE statement, we are back to using the DATALINES statement to read the data instream. But, in order to still be able to take advantage of the INFILE statement's options, we have to tell SAS to expect the data after the DATALINES statement. That's why the:

infile DATALINES ...;

statement.

We need to use the INFILE statement just so we can take advantage of the statement's MISSOVER option. Just as is the case for column and formatted input, the MISSOVER option tells SAS not to advance to the next record when it doesn't find values in the current line for all of the INPUT statement variables. At the end of the current record, values that are expected but not found are set to missing.

One more thing to note is the range of numeric variables:

Week1-Week5

that appears in the INPUT statement. When the variable values in the input raw data file are sequential and separated by a blank (or some other such delimiter), you can specify a range of variables in the INPUT statement, as we did in the above program. This is especially useful if your data contains similar variables. For example, if your data contain the answers to a questionnaire, you might want to call your variables q1, q2, q3, ... . Note that you can also specify a range of variables in the PRINT procedure's VAR statement.

Finally, launch and run   the SAS program, and review the output to convince yourself that the data are read in properly.

It is important to keep in mind that the MISSOVER option works only for missing values that occur at the end of the record. If your data contains missing values at the beginning or middle of a record, then you need instead to take advantage of the INFILE statement's DSD option.

Example 20.10

 The following program uses list input and the INFILE statement's DSD option to read in the results of a survey of five people when some of the values are missing in the middle of a record:

DATA survey;
	infile DATALINES DLM=',' DSD;
	input Name $ (Q1-Q5) ($);
	DATALINES;
Robert,,A,C,A,D
William,B,C,A,D,A
Linda,C,B,,A,C
Lisa,D,D,D,C,A
Katherine,A,B,C,D,A
RUN;
 
PROC PRINT data = survey;
	title 'Survey Results';
	var Name Q1-Q5;
RUN;

Output

First, review the data to note that Robert and Linda each have one missing value in the middle of their record. Also, note that the data values are delimited with commas now rather than blanks. That's why the:

DLM=','

option appears in the INFILE statement. You might recall from Stat 480 that DLM stands for delimiter. Then, you should notice the range of variables:

(Q1-Q5) ($)

that appears in the INPUT statement. We've used the proper syntax for specifying a range of character variables, namely both the variable list and the $ sign are enclosed in parentheses.

Hmmm ... what else? Oh yeah ... the whole point of this example ... the DSD option that appears in the INFILE statement. The DSD option changes how SAS treats delimiters when list input is used. Specifically, the DSD option:

• sets the default delimiter to a comma
• treats two consecutive delimiters as a missing value
• removes quotation marks from values

That first point clearly makes our inclusion of the DLM=',' option superfluous, but that's okay. It's the second point that is the most relevant and the one that makes our program here work. Let's check it out! Launch and run   the SAS program, and review the output to convince yourself that the data are read in properly. Oops ... not quite! The name Katherine contains nine characters. Because SAS defines the length of character variables to be 8 bytes by default, SAS truncates her name and stores it as Katherin without the ending e. We'll learn how to rectify that problem soon, but first one more example of the DSD option.

Example 20.11

The following program illustrates that the DSD option can also be used i) when there is a missing value at the beginning of a record, and ii) when the data are delimited by blanks (in conjunction with the DLM= option):

DATA survey;
	infile DATALINES DLM=' ' DSD;
	input Name $ (Q1-Q5) ($);
	DATALINES;
Robert  A C A D
William B C A D A
Linda C B  A C
 D D D C A
Katherine A B C D A
RUN;
PROC PRINT data = survey;
	title 'Survey Results';
	var Name Q1-Q5;
RUN;

Output

The only difference between this program and the previous one is that the comma delimiters have been replaced with blanks, and the first value (Lisa) in the fourth record has been deleted so that our data set contains a missing value at the beginning of a record. Because the data are now delimited with blanks we use the DLM = ' ' option. Launch and run   the SAS program, and review the output to convince yourself that the data are read in properly. Oops ... let's go fix that problem with Katherine now.

Example 20.12

The following program is identical to the previous program, except a LENGTH statement has been added to tell SAS to define the length of the character variable Name to be 9 rather than the default 8:

DATA survey;
	infile DATALINES DLM=' ' DSD;
	length Name $ 9;
	input Name $ (Q1-Q5) ($);
	DATALINES;
Robert  A C A D
William B C A D A
Linda C B  A C
 D D D C A
Katherine A B C D A
RUN;
 
PROC PRINT data = survey;
	title 'Survey Results';
	var Name Q1-Q5;
RUN;

Output

Note that the LENGTH statement was intentionally placed before the INPUT statement. That's because variable attributes are defined when the variable is first encountered in the DATA step. If we placed the LENGTH statement after the INPUT statement, SAS would therefore by default define the length of Name to be 8 characters, not the desired 9. As you can see by the inclusion of the dollar sign ($), the LENGTH statement also tells SAS that Name should be treated as a character variable.

Now, go ahead and launch and run   the SAS program, and review the output to convince yourself that all of the data are read in properly this time!

Example 20.14 

The following program uses list input modified with an ampersand (&) to read in the city and the population values of the ten most populous cities in the United States in the year 2000:

DATA citypops;
	infile DATALINES FIRSTOBS = 2;
	length city $ 12;
	input city & pop2000;
	DATALINES;
City  Yr2000Popn
New York  8008278
Los Angeles  3694820
Chicago  2896016
Houston  1953631
Philadelphia  1517550
Phoenix  1321045
San Antonio  1144646
San Diego  1223400
Dallas  1188580
San Jose  894943
;
RUN;
 
PROC PRINT data = citypops;
	title 'The citypops data set';
	format pop2000 comma10.;
RUN;

Output

Comparing this program to the previous program you should note four differences:

1. The LENGTH statement tells SAS, in the compile phase, to define the city variable as a character variable, and to expect the city names to be as long as 12 characters.
2. The ampersand (&) that follows the city variable in the INPUT statement tells SAS that the city values may contain one or more single embedded blanks. Because the ampersand modifier is used, SAS will read the city value until two or more consecutive blanks are encountered. That is a very important point ... when you use ampersand modified list input, the values that you are reading in must be separated by two or more consecutive blanks. You cannot use any other delimiter to indicate the end of each field.
3. The commas have been removed from the population values so that SAS can read in the population values using unmodified (standard) list input for the pop2000 variable.
4. A FORMAT statement has been added to the PRINT procedure just so that the pop2000 values are displayed with commas.

Launch and run   the SAS program, and review the output to convince yourself that the values for both the city and pop2000 variables are read in properly.

Example 20.15

Rather than using a LENGTH statement to define the type and length of the city variable, we can place a $w. character informat right in the INPUT statement. The only difference between the following program and the previous one is that the LENGTH statement has been removed, and the $12. character informat has been inserted into the INPUT statement immediately following the city variable's ampersand (&) modifier:

DATA citypops;
infile DATALINES FIRSTOBS = 2;
input city & $12. pop2000;
DATALINES;
City  Yr2000Popn
New York  8008278
Los Angeles  3694820
Chicago  2896016
Houston  1953631
Philadelphia  1517550
Phoenix  1321045
San Antonio  1144646
San Diego  1223400
Dallas  1188580
San Jose  894943
;
RUN;
 
PROC PRINT data = citypops;
	title 'The citypops data set';
	format pop2000 comma10.;
RUN;

Output

Launch and run   the SAS program, and review the output to convince yourself that the values for both the city and pop2000 variables are again read in properly.

Example 20.1

The following program uses the colon (:) modifier to tell SAS to expect commas when reading in the values for the pop2000 variable:

DATA citypops;
	infile DATALINES FIRSTOBS = 2;
	input city & $12. pop2000 : comma.;
	DATALINES;
City  Yr2000Popn
New York  8,008,278
Los Angeles  3,694,820
Chicago  2,896,016
Houston  1,953,631
Philadelphia  1,517,550
Phoenix  1,321,045
San Antonio  1,144,646
San Diego  1,223,400
Dallas  1,188,580
San Jose  894,943
;
RUN;

PROC PRINT data = citypops;
	title 'The citypops data set';
	format pop2000 comma10.;
RUN;

Output

Comparing this program to the previous program you should note just two differences:

1. The commas have been added back into the population values so that we can see how to use the colon (:) modifier to read in nonstandard data values while still using list input.
2. The colon (:) and comma. informat that follows the pop2000 variable in the INPUT statement tells SAS to expect population values to contain nonstandard characters — commas, in this particular instance. As illustrated here, we need not specify a w value when using the COMMAw.d informat here. That's because list input just reads each value until a blank is detected. (This differs from using a numeric informat with formatted input, in which we must specify a w value in order to tell SAS how many columns to read.)

Launch and run   the SAS program, and review the output to convince yourself that the values for both the city and pop2000 variables are again read in properly.