Using the FILEVAR= Option

Overview

You can make the process of concatenating raw data files more flexible by using an INFILE statement with the FILEVAR= option. The FILEVAR= option enables you to dynamically change the currently opened input file to a new input file.

General form, INFILE statement with the FILEVAR= option:

INFILE file-specification FILEVAR= variable;

Here is an explanation of the syntax:

FILEVAR= variable

names a variable whose change in value causes the INFILE statement to close the current input file and open a new input file.

variable

contains a character string that is a physical filename.

When you use an INFILE statement with the FILEVAR= option, the file specification is a placeholder, not an actual filename or fileref that had been assigned previously to a file. SAS uses this placeholder for reporting processing information to the SAS log. The file specification must conform to the same rules as a fileref.

When the INFILE statement executes, it reads from the file that the FILEVAR= variable specifies. Like automatic variables, this variable is not written to the data set.

Example

Suppose you want to create a SAS data set that contains three months of data stored in three raw data files. The three months are the current month, and the previous two months are a rolling quarter.

In the following INFILE statement, Temp is an arbitrarily named placeholder, not an actual filename or fileref that had been assigned to a file previously. The FILEVAR= variable Nextfile contains the name of the raw data file to read (for example, Month9.dat, Month10.dat, or Month11.dat). A RUN statement is not included because the program is not complete.

data work.quarter;
   infile temp filevar=nextfile;
   input Flight $ Origin $ Dest $
         Date : date9. RevCargo : comma15.;

Table 14.5 Raw Data File Month9.dat (first five records)
----+----10---+----20---+----30---+----40 IA10200 SYD HKG 01SEP2000 $189,441.00 IA10201 SYD HKG 01SEP2000 $175,473.00 IA10300 SYD CBR 01SEP2000 $1,370.00 IA10301 SYD CBR 01SEP2000 $710.00 IA10302 SYD CBR 01SEP2000 $1,210.00

Table 14.5 Raw Data File Month9.dat (first five records)

----+----10---+----20---+----30---+----40 
IA10200 SYD HKG 01SEP2000 $189,441.00 
IA10201 SYD HKG 01SEP2000 $175,473.00 
IA10300 SYD CBR 01SEP2000 $1,370.00 
IA10301 SYD CBR 01SEP2000 $710.00 
IA10302 SYD CBR 01SEP2000 $1,210.00

Table 14.6 Raw Data File Month10.dat (first five records)
----+----10---+----20---+----30---+----40 IA10200 SYD HKG 01OCT2000 $182,457.00 IA10201 SYD HKG 01OCT2000 $160,923.00 IA10300 SYD CBR 01OCT2000 $1,030.00 IA10301 SYD CBR 01OCT2000 $870.00 IA10302 SYD CBR 01OCT2000 $770.00

Table 14.6 Raw Data File Month10.dat (first five records)

----+----10---+----20---+----30---+----40 
IA10200 SYD HKG 01OCT2000 $182,457.00 
IA10201 SYD HKG 01OCT2000 $160,923.00 
IA10300 SYD CBR 01OCT2000 $1,030.00 
IA10301 SYD CBR 01OCT2000 $870.00 
IA10302 SYD CBR 01OCT2000 $770.00

Table 14.7 Raw Data File Month11.dat (first five records)
----+----10---+----20---+----30---+----40 IA10200 SYD HKG 01NOV2000 $176,637.00 IA10201 SYD HKG 01NOV2000 $164,997.00 IA10300 SYD CBR 01NOV2000 $1,230.00 IA10301 SYD CBR 01NOV2000 $1,230.00 IA10302 SYD CBR 01NOV2000 $790.00

Table 14.7 Raw Data File Month11.dat (first five records)

----+----10---+----20---+----30---+----40 
IA10200 SYD HKG 01NOV2000 $176,637.00 
IA10201 SYD HKG 01NOV2000 $164,997.00 
IA10300 SYD CBR 01NOV2000 $1,230.00 
IA10301 SYD CBR 01NOV2000 $1,230.00 
IA10302 SYD CBR 01NOV2000 $790.00

Note: You can also use multiple INFILE statements or operating system techniques to combine raw data files. This chapter discusses only the FILENAME statement and the INFILE statement with the FILEVAR= option.

Assigning the Names of the Files to Read

The next step is to assign the names of the three files to read to the variable Nextfile:

 data work.quarter;
    infile temp filevar=nextfile;
    input Flight $ Origin $ Dest $ 
          Date : date9. RevCargo : comma15.;

In this case, use the raw data files Month9.dat, Month10.dat, and Month11.dat. Notice that the titles of the raw data files are very similar. They each start with “Month” and are followed by numerals and the file extension .dat:

Month9.dat
Month10.dat
Month11.dat

You can use an iterative DO loop and the PUT function to automatically change the values that are assigned to Nextfile.

Example

In the following code, the DO statement creates the index variable Month and assigns it the values of 9, 10, and 11. The assignment statement then assigns the name of the raw data file to Nextfile using the current value of Month and the PUT function. The PUT function converts the numeric value of Month to a text value with a length of 2.

Month9.dat, Month10.dat, and Month11.dat are stored in the C:Sasuser directory in the Windows operating environment. On the right side of the assignment statement, the text string c:sasusermonth is concatenated with the current value of Month using the double exclamation point (!!) concatenation operator. c:sasusermonthMonth is then concatenated with the text string .dat.

data work.quarter;
   do Month = 9, 10, 11;
      nextfile="c:sasusermonth"
               !!put(Month,2.)!!".dat";
      infile temp filevar=nextfile;
      input Flight $ Origin $ Dest $ 
            Date : date9. RevCargo : comma15.;
   end;

The following table shows the value of Nextfile as the value of Month changes.

When Month=	Nextfile=
9	c:sasuserMonth 9.dat
10	c:sasuserMonth10.dat
11	c:sasuserMonth11.dat

Tip

Depending on the characters that are available on your keyboard, the symbol that you use as the concatenation operator can be a double vertical bar (||), a double broken vertical bar (¦¦), or a double exclamation point (!!).

Using the COMPRESS Function

Note the space between Month and 9 in c:sasusermonth 9.dat. You can eliminate the space by using the COMPRESS function.

When Month=	Nextfile=
9	c:sasuserMonth 9.dat
10	c:sasuserMonth10.dat
11	c:sasuserMonth11.dat

General form, COMPRESS function:

COMPRESS(source, <characters-to-remove>);

Here is an explanation of the syntax:

source

specifies a source string that contains the characters to remove.

characters-to-remove

specifies the character or characters that SAS removes from the source string.

Note: If a value for characters-to-remove is omitted, the COMPRESS function removes blank spaces from the source.

Example

In the following code, the COMPRESS function removes blank spaces from the value of Nextfile:

data work.quarter;
   do Month = 9, 10, 11;
      nextfile="c:sasusermonth"!!put(Month,2.)!!".dat";
      nextfile=compress (nextfile,' '),
      infile temp filevar=nextfile;
      input Flight $ Origin $ Dest $ 
            Date : date9. RevCargo : comma15.;
      end;

The COMPRESS function can be combined with the assignment statement for greater efficiency:

data work.quarter;
   do Month = 9, 10, 11;
      nextfile="c:sasusermonth"!!compress(put(Month,2.)!!".dat",' '),
      infile temp filevar=nextfile;
      input Flight $ Origin $ Dest $ 
            Date : date9. RevCargo : comma15.;
      end;

With the addition of the COMPRESS function, when the value of Month equals 9, Nextfile is assigned the correct value, c:sasusermonth9.dat.

When Month=	Nextfile=
9	c:sasuserMonth9.dat
10	c:sasuserMonth10.dat
11	c:sasuserMonth11.dat

An OUTPUT statement within the DO loop outputs each observation to the SAS data set Work.Quarter. A STOP statement prevents an infinite loop of the DATA step.

data work.quarter;
   do Month = 9, 10, 11;
      nextfile="c:sasusermonth"
               !!compress(put(Month,2.)!!".dat",' '),
      infile temp filevar=nextfile;
      input Flight $ Origin $ Dest $ Date : date9.
            RevCargo : comma15.;
      output;
   end;
   stop;

The program is almost complete.

Using the END= Option

When you read past the last row in an input file, the DATA step normally stops processing. In this example, we are reading three raw data files. We must not read past the last record in the first two files, because that would cause the DATA step to prematurely stop processing. You can use the END= option with the INFILE statement to determine when you are reading the last record in the last raw data file.

General form, INFILE statement with the END= option:

INFILE file-specification END=variable;

Here is an explanation of the syntax:

variable

names a variable that SAS sets to these values:

0 (false) when the current input data record is not the last record in the input file
1 (true) when the current input record is the last record in the input file.

Note: Like automatic variables, the END= variable is not written to the SAS data set.

You can test the value of the END= variable to determine whether the DATA step should continue processing.

Example

The END= variable Lastobs is created in the INFILE statement. The DO UNTIL statement conditionally executes until the value of Lastobs equals 1 (true). A RUN statement completes the program.

data work.quarter; 
   do Month = 9, 10, 11; 
   nextfile="c:sasusermonth"
            !!compress(put(Month,2.)!!".dat",' '),
         do until (lastobs); 
         infile temp filevar=nextfile end=lastobs;
         input Flight $ Origin $ Dest $ Date : date9.
               RevCargo : comma15.;
         output;
      end;
   end;
   stop;
run;

PROC PRINT output shows a portion of the observations in the SAS data set Work.Quarter. Notice that the variables Nextfile and Lastobs are not written to the data set.

 proc print 
     data=work.quarter 
     (firstobs=45 obs=55);
     format date date9. 
          revcargo dollar11.2;
run;

Using Date Functions

You can make your program more flexible by eliminating the need to include explicit month numbers in your SAS statements. To create a program that always reads the current month and the previous two months, you can use date functions to obtain the current month number to begin the rolling quarter.

Example

In the following program, the MONTH and TODAY functions are used to obtain the value of the variable Monthnum. The TODAY function returns the current date from the system clock as a SAS date value. The month number is then extracted from the current date using the MONTH function.

The value of Midmon is calculated by subtracting 1 from the value of Monthnum. The value of Lastmon is then calculated by subtracting 1 from the values of Midmon. The following table shows the values Monthnum, Midmon, and Lastmon if the current date is October 22, 2013.

In the previous example, the DO statement created the index variable Month and assigned it the values of 9, 10, and 11. Here, the DO statement assigns Month the values of Monthnum, Midmon, and Lastmon:

data work.quarter (drop=monthnum midmon lastmon); 
   monthnum=month(today());
   midmon=monthnum-1;
   lastmon=midmon-1;
   do Month = monthnum, midmon, lastmon; 
      nextfile="c:sasusermonth"
               !!compress(put(Month,2.)!!".dat",' '),        
      do until (lastobs); 
         infile temp filevar=nextfile end=lastobs;
         input Flight $ Origin $ Dest $ Date : date9.
               RevCargo : comma15.;
         output;
      end;
   end;
   stop;
run;

The following PROC PRINT output shows a portion of the observations in Work.Quarter.

proc print data=work.quarter 
           (firstobs=45 obs=55);
   format date date9. 
          revcargo dollar11.2;
run;

Using the INTNX Function

In the previous example the current month was October. What happens if the current month is January or February?

Suppose the current date is February 16, 2013. Using the following program, the values for Midmon (January) and Lastmon (December) would be 1 and 0 respectively. Since there is no “0” month, the program would fail to read the third raw data file.

data work.quarter (drop=monthnum midmon lastmon); 
   thisday=today(); 
   monthnum=month(thisday);
   midmon=month(intnx('month',thisday,-1));
   lastmon=month(intnx('month',thisday,-2));
   do Month = monthnum, midmon, lastmon;
      nextfile="c:sasusermonth"
               !!compress(put(Month,2.)!!".dat",' '),         
      do until (lastobs); 
         infile temp filevar=nextfile end=lastobs;
         input Flight $ Origin $ Dest $ Date : date9.
               RevCargo : comma15.;
         output;
      end;
   end;
   stop;
run;

Values Assigned to the Monthnum, Midmon, and Lastmon Variables

You can use the INTNX function with the TODAY and MONTH functions to correctly determine the values of Midmon and Lastmon for any given date. Remember that the INTNX function increments a date, time, or datetime value by a given interval or intervals, and returns a date, time, or datetime value.

Example

Suppose the current date is January 30, 2013. In the following program Monthnum is assigned a value of 1 using the TODAY and MONTH functions. The INTNX function is used with the TODAY and MONTH functions to assign a value of 12 to Midmon and a value of 11 to Lastmon.

data work.quarter (drop=monthnum midmon lastmon);
   monthnum=month(today());
   midmon=month(intnx('month',today(),-1));
   lastmon=month(intnx('month',today(),-2));
   do Month = monthnum, midmon, lastmon;
      nextfile="c:sasmonth"!!compress(put(Month,2.)!!".dat",' '),
      do until (lastobs); 
         infile temp filevar=nextfile end=lastobs;
         input Flight $ Origin $ Dest $ Date : date9.
               RevCargo : comma15.;
         output;
      end;
   end;
   stop;
run;

The following PROC PRINT output shows a portion of the observations in Work.Quarter.

proc print data=work.quarter 
           (firstobs=45 obs=55);
   format date date9. 
          revcargo dollar11.2;
run;

Table of Contents for Using the FILEVAR= Option

Create new playlist

Sign In

Sign Up

Using the FILEVAR= Option

Overview

Example

Assigning the Names of the Files to Read

Example

Using the COMPRESS Function

Example

Using the END= Option

Example

Using Date Functions

Example

Using the INTNX Function

Example

Table of Contents for
Using the FILEVAR= Option