SAS Statistics by Example. Ron Cody, EdD

Reading CSV Files

      You can make a very small change to Program 1.3 to read the same data from a CSV file. Following is a listing of such a file:

      A CSV Text File: c:\books\Statistics by Example\comma.csv

      Notice that you no longer need the period in subject 6 because, in the tradition of CSV files, two commas in a row indicate a missing value.

      The only change you need to make to Program 1.3 is to use an option called DSD on the INFILE statement. The DSD option specifies that two consecutive commas represent a missing value and that the default delimiter is a comma. Here is the modified program:

Program 1.5: Reading a CSV File

      This program produces a SAS data set identical to the one created by Program 1.3.

      If your CSV file contains variable names in the first row, then the Import Wizard uses these variable names when it creates the SAS data set. Actually, you can use the Import Wizard even if the first row does not contain variable names. If you do, SAS will name the variables F1, F2, etc. This approach is not recommended.

Data Values in Fixed Columns

      You might have a raw text file in which the value for each variable is in a fixed column. SAS has two methods for reading this type of data: column input and formatted input. For column input, you follow each variable name on the INPUT statement with the starting and ending column for that value. If you want to create a character variable, you place a dollar sign between the variable name and the column specifications.

      For example, if you have ID data in columns 1–3, Age in columns 4–6, and Gender in column 7 of your raw data file, your input statement might look like this:

      input ID $ 1-3 Age 4-6 Gender $ 7;

      Stylistically, you might prefer to write this statement on three lines, like this (so that the variable names line up):

      input ID $ 1-3

       Age 4-6

       Gender $ 7;

      For formatted input, you specify the starting column for the variable using an at sign (@) (called a column pointer) followed by the starting column number. Next, you put your variable name, followed by a SAS informat—a specification of how to read and interpret the next n columns. An equivalent statement to read the same data for ID, Age, and Gender using formatted input is:

      input @1 ID $3.

       @4 Age 3.

       @7 Gender $1.;

      The informat $3. tells SAS to read three columns of character data; the 3. informat says to read three columns of numeric data; the $1. informat says to read one column of character data. The two informats n. and $n., are used to read n columns of numeric and character data, respectively.

      The INPUT statement is actually quite powerful and enables you to read both simple and complex data structures. For a complete description of how the INPUT statement works, see Learning SAS by Example: A Programmer’s Guide or one of the other publications available from SAS Press.

Excel Files with Invalid SAS Variable Names

      What if your Excel file contains variable names in the first row that are not valid SAS names? Take a look at the following spreadsheet: