Examples: IMPORT Procedure

Example 1: Importing a Delimited External File

Procedure features:

• PROC IMPORT statement arguments:

  • DATAFILE=

    OUT=

    DBMS=

    REPLACE

• Data source statements:

  • DELIMITER =

    GETNAMES=

Other features:

• PRINT procedure

This example imports the following delimited external file and creates a temporary

SAS data set named WORK.MYDATA:

 Region&State&Month&Expenses&Revenue  Southern&GA&JAN2001&2000&8000  Southern&GA&FEB2001&1200&6000  Southern&FL&FEB2001&8500&11000  Northern&NY&FEB2001&3000&4000  Northern&NY&MAR2001&6000&5000  Southern&FL&MAR2001&9800&13500  Northern&MA&MAR2001&1500&1000 

Program

Specify the input file.

 proc import datafile="C:\My Documents\myfiles\delimiter.txt" 

Identify the output SAS data set.

 out=mydata 

Specify that the input file is a delimited external file.

 dbms=dlm 

Overwrite the data set if it exists.

 replace; 

Specify the delimiter. The DELIMITER= option specifies that an & (ampersand) delimits data fields in the input file. The delimiter separates the columns of data in the input file.

 delimiter=;&;; 

Generate the variable names from the first row of data in the input file.

 getnames=yes;  run; 

Print the WORK.MYDATA data set. PROC PRINT produces a simple listing.

 options nodate ps=60 ls=80;  proc print data=mydata;  run; 

SAS Log

The SAS log displays information about the successful import. For this example, PROC IMPORT generates a SAS DATA step, as shown in the partial log that follows .

 /**********************************************************************  79     *   PRODUCT:   SAS  80     *   VERSION:   9.00  81     *   CREATOR:   External File Interface  82     *   DATE:      24JAN02  83     *   DESC:      Generated SAS Datastep Code  84     *   TEMPLATE SOURCE: (None Specified.)  85     ***********************************************************************/  86        data MYDATA ;  87       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */  88        infile 'C:\My Documents\myfiles\delimiter.txt' delimiter = '&' MISSOVER  88 !   DSD lrecl=32767 firstobs=2 ;  89           informat Region . ;  90           informat State . ;  91           informat Month . ;  92           informat Expenses best32. ;  93           informat Revenue best32. ;  94           format Region . ;  95           format State . ;  96           format Month . ;  97           format Expenses best12. ;  98           format Revenue best12. ;  99        input  100                   Region $  101                   State $  102                   Month $  103                   Expenses  104                   Revenue  105       ;  106       if _ERROR_ then call symput('_EFIERR_',1); /* set ERROR detection  106! macro variable */  107      run;  NOTE: Numeric values have been converted to character        values at the places given by: (Line):(Column).        106:44  NOTE: The infile 'C:\My Documents\myfiles\delimiter.txt' is:        File Name=C:\My Documents\myfiles\delimiter.txt,        RECFM=V,LRECL=32767  NOTE: 7 records were read from the infile 'C:\My        Documents\myfiles\delimiter.txt'.        The minimum record length was 29.        The maximum record length was 31.  NOTE: The data set WORK.MYDATA has 7 observations and 5 variables.  NOTE: DATA statement used (Total process time):        real time           0.04 seconds        cpu time            0.05 seconds  7 rows created in MYDATA                                    from C:\My  Documents\myfiles\delimiter.txt.  NOTE: .MYDATA was successfully created. 

Output

This output lists the output data set, MYDATA, created by PROC IMPORT from the delimited external file.

 The SAS System  Obs  Region    State  Month    Expenses  Revenue   1   Southern   GA    JAN2001      2000     8000   2   Southern   GA    FEB2001      1200     6000   3   Southern   FL    FEB2001      8500    11000   4   Northern   NY    FEB2001      3000     4000   5   Northern   NY    MAR2001      6000     5000   6   Southern   FL    MAR2001      9800    13500   7   Northern   MA    MAR2001      1500     1000 

Example 2: Importing a Specific Spreadsheet from an Excel Workbook

Procedure features:

• PROC IMPORT statement arguments:

  • DATAFILE=

    OUT=

• Data source statements:

  • SHEET=

    GETNAMES=

Other features:

• PRINT procedure option:

  • OBS=

This example imports a specific spreadsheet from an Excel workbook, which contains multiple spreadsheets, and creates a new, permanent SAS data set named SASUSER.ACCOUNTS.

Program

Specify the input file. The filename contains the extension .XLS, which PROC IMPORT recognizes as identifying an Excel 2000 spreadsheet.

 proc import datafile="c:\myfiles\Accounts.xls" 

Identify the output SAS data set.

 out=sasuser.accounts; 

Import only the sheet PRICES that is contained in the file ACCOUNTS.XLS.

 sheet='Prices'; 

Do not generate the variable names from the input file. PROC IMPORT will use default variable names.

 getnames=no;  run; 

Print the SASUSER.ACCOUNTS data set. PROC PRINT produces a simple listing. The OBS= data set option limits the output to the first 10 observations.

 proc print data=sasuser.accounts(obs=10);  run; 

Output

The following output displays the first 10 observations of the output data set, SASUSER.ACCOUNTS:

 The SAS System                     1  OBS  F1                                    F2                   F3    1  Dharamsala Tea                        10 boxes x 20 bags   18.00    2  Tibetan Barley Beer                   24 - 12 oz bottles   19.00    3  Licorice Syrup                        12 - 550 ml bottles  10.00    4  Chef Anton's Cajun Seasoning          48 - 6 oz jars       22.00    5  Chef Anton's Gumbo Mix                36 boxes             21.35    6  Grandma's Boysenberry Spread          12 - 8 oz jars       25.00    7  Uncle Bob's Organic Dried Pears       12 - 1 lb pkgs.      30.00    8  Northwoods Cranberry Sauce            12 - 12 oz jars      40.00    9  Mishi Kobe Beef                       18 - 500 g pkgss.    97.00   10  Fish Roe                              12 - 200 ml jars     31.00 

Example 3: Importing a Subset of Records from an Excel Spreadsheet

Procedure features:

• PROC IMPORT statement arguments:

  • DATAFILE=

  • OUT=

This example imports a subset of an Excel spreadsheet and creates a temporary SAS data set. The WHERE= SAS data set option is specified in order to import only a subset of records from the Excel spreadsheet.

Program

Specify the input file.

 proc import datafile='c:\Myfiles\Class.xls' 

Identify the output SAS data set, and request that only a subset of the records be imported.

 out=work.femaleclass (where=(sex='F'));  run; 

Print the new SAS data set. PROC PRINT produces a simple listing.

 proc print data=work.femaleclass;  run; 

Output

The following output displays the output SAS data set, WORK.FEMALECLASS:

 The SAS System                          1  Obs  Name    Sex    Age    Height  Weight   1   Alice    F      13     56.5     84.0   2   Barbara  F      13     65.3     98.0   3   Carol    F      14     62.8    102.5   4   Jane     F      12     59.8     84.5   5   Janet    F      15     62.5    112.5   6   Joyce    F      11     51.3     50.5   7   Judy     F      14     64.3     90.0   8   Louise   F      12     56.3     77.0   9   Mary     F      15     66.5    112.0 

Example 4: Importing a Microsoft Access Table

Procedure features:

• PROC IMPORT statement arguments:

  • TABLE=

    OUT=

    DBMS=

• Data source Statements:

  • DATABASE=

    PWD=

    UID=

    WGDB=

This example imports a Microsoft Access 97 table and creates a permanent SAS data set named SASUSER.CUST. The Access table has user -level security, so it is necessary to specify values for the PWD=, UID=, and WGDB= statements.

Program

Specify the input DBMS table name.

 proc import table="customers" 

Identify the output SAS data set.

 out=sasuser.cust 

Specify that the input file is a Microsoft Access 97 table.

 dbms=access97; 

Identify the user ID to the DBMS.

 uid="userid"; 

Specify the DBMS password to access the table.

 pwd="mypassword"; 

Specify the path and filename of the database that contains the table.

 database="c:\myfiles\east.mdb"; 

Specify the workgroup (security) database name that contains the user ID and password data for the Microsoft Access table.

 wgdb="c:\winnt\system32\security.mdb"; 

Print the SASUSER.CUST data set. PROC PRINT produces a simple listing. The OBS= data set option limits the output to the first five observations.

 proc print data=sasuser.cust(obs=5);  run; 

Output

The following output displays the first five observations of the output data set, SASUSER.CUST.

 The SAS System                     1  Obs  Name                       Street               Zipcode    1  David Taylor               124 Oxbow Street     72511    2  Theo Barnes                2412 McAllen Avenue  72513    3  Lydia Stirog               12550 Overton Place  72516    4  Anton Niroles              486 Gypsum Street    72511    5  Cheryl Gaspar              36 E. Broadway       72515 

Example 5: Importing a Specific Spreadsheet from an Excel Workbook on a PC Server

Procedure features:

• PROC IMPORT statement arguments:

  • DATAFILE=

    OUT=

Data Source Statements:

• SERVER=

  SERVICE=

  SHEET=

  GETNAMES=

Other features:

• PRINT procedure option:

  • OBS=

This example imports a specific spreadsheet from an Excel workbook on a PC server, which contains multiple spreadsheets, and creates a new, permanent SAS data set named WORK.PRICES.

Program

 proc import dbms=excelcs            datafile="c:\myfiles\Invoice.xls"            out=work.prices;       server='Sales';       service='pcfiles';       sheet='Prices';       getnames=yes;       usedate=no;  run;  proc print data=work.prices(obs=10);  run;