Examples: EXPORT Procedure

Example 1: Exporting a Delimited External File

Procedure features:

• PROC EXPORT statement arguments:

  • DATA=

    DBMS=

    OUTFILE=

• Data source statement:

  • DELIMITER =

This example exports the following SAS data set named SASHELP.CLASS and creates a delimited external file:

Output 20.1: PROC PRINT of SASHELP.CLASS

 The SAS System                                  1  Obs   Name      Sex               Age          Height          Weight    1   Alfred     M                 14              69           112.5    2   Alice      F                 13            56.5              84    3   Barbara    F                 13            65.3              98    4   Carol      F                 14            62.8           102.5    5   Henry      M                 14            63.5           102.5    6   James      M                 12            57.3              83    7   Jane       F                 12            59.8            84.5    8   Janet      F                 15            62.5           112.5    9   Jeffrey    M                 13            62.5              84   10   John       M                 12              59            99.5   11   Joyce      F                 11            51.3            50.5   12   Judy       F                 14            64.3              90   13   Louise     F                 12            56.3              77   14   Mary       F                 15            66.5             112   15   Philip     M                 16              72             150   16   Robert     M                 12            64.8             128   17   Ronald     M                 15              67             133   18   Thomas     M                 11            57.5              85   19   William    M                 15            66.5             112 

 

Program

Identify the input SAS data set, specify the output filename, and specify the type of file. Note that the filename does not contain an extension. DBMS=DLM specifies that the output file is a delimited external file.

 proc export data=sashelp.class     outfile='c:\myfiles\class'     dbms=dlm; 

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

 delimiter='&';  run; 

SAS Log

The SAS log displays the following information about the successful export. Notice the generated SAS DATA step.

 47    /**********************************************************************  48    *     PRODUCT:   SAS  49    *     VERSION:   9.00  50    *     CREATOR:   External File Interface  51    *     DATE:      07FEB02  52    *     DESC:      Generated SAS Datastep Code  53    *     TEMPLATE SOURCE:  (None Specified.)  54    ***********************************************************************/  55       data _null_;  56       set   SASHELP.CLASS                               end=EFIEOD;  57       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */  58       %let _EFIREC_ = 0;     /* clear export record count macro variable */  59       file c:\myfiles\class delimiter=& DSD DROPOVER  59 ! lrecl=32767;  60          format Name . ;  61          format Sex . ;  62          format Age best12. ;  63          format Height best12. ;  64          format Weight best12. ;  65       if _n_ = 1 then        /* write column names */  66        do;  67          put  68          Name  69          &  70          Sex  71          &  72          Age  73          &  74          Height  75          &  76          Weight  77          ;  78        end;  79        do;  80          EFIOUT + 1;  81          put Name $ @;  82          put Sex $ @;  83          put Age @;  84          put Height @;  85          put Weight ;  86          ;  87        end;  88       if _ERROR_ then call symput(_EFIERR_,1);   /* set ERROR detection  88 ! macro variable */  89       If EFIEOD then  90          call symput(_EFIREC_,EFIOUT);  91       run;  NOTE: Numeric values have been converted to character        values at the places given by: (Line):(Column).        88:44     90:31  NOTE: The file c:\myfiles\class is:        File Name=c:\myfiles\class,        RECFM=V,LRECL=32767  NOTE: 20 records were written to the file c:\myfiles\class.        The minimum record length was 17.        The maximum record length was 26.  NOTE: There were 19 observations read from the data set SASHELP.CLASS.  NOTE: DATA statement used (Total process time):        real time           0.13 seconds        cpu time            0.05 seconds  19 records created in c:\myfiles\class from SASHELP.CLASS                   .  NOTE: c:\myfiles\class was successfully created. 

Output

The external file produced by PROC EXPORT follows .

 Name&Sex&Age&Height&Weight  Alfred&M&14&69&112.5  Alice&F&13&56.5&84  Barbara&F&13&65.3&98  Carol&F&14&62.8&102.5  Henry&M&14&63.5&102.5  James&M&12&57.3&83  Jane&F&12&59.8&84.5  Janet&F&15&62.5&112.5  Jeffrey&M&13&62.5&84  John&M&12&59&99.5  Joyce&F&11&51.3&50.5  Judy&F&14&64.3&90  Louise&F&12&56.3&77  Mary&F&15&66.5&112  Philip&M&16&72&150  Robert&M&12&64.8&128  Ronald&M&15&67&133  Thomas&M&11&57.5&85  William&M&15&66.5&112 

Example 2: Exporting a Subset of Observations to an Excel Spreadsheet

Procedure features:

• PROC EXPORT statement arguments:

  • DATA=

  • DBMS=

  • OUTFILE=

  • REPLACE

This example exports the SAS data set SASHELP.CLASS, shown in Output 20.1. PROC EXPORT creates an Excel file named Femalelist.xsl, and by default, creates a spreadsheet named Class. Since the SHEET= data source statement is not specified, PROC EXPORT uses the name of the SAS data set as the spreadsheet name. The WHERE= SAS data set option is specified in order to export a subset of the observations, which results in the spreadsheet containing only the female students.

Program

Identify the input SAS data set, request a subset of the observations, specify the output data source, specify the output file, and overwrite the target spreadsheet if it exists. The output file is an Excel 2000 spreadsheet.

 proc export data=sashelp.class (where=(sex='F'))     outfile='c:\myfiles\Femalelist.xls'     dbms=excel     replace;  run; 

Example 3: Exporting to a Specific Spreadsheet in an Excel Workbook

Procedure features:

• PROC EXPORT statement arguments:

  • DATA=

  • DBMS=

  • OUTFILE=

• Data Source Statement:

  • SHEET=

This example exports a SAS data set named MYFILES.GRADES1 and creates an Excel 2000 workbook named Grades.xsl. MYFILES.GRADES1 becomes one spreadsheet in the workbook named Grades1.

Program

Identify the input SAS data set, specify the output data source, and specify the output file.

 proc export data=myfiles.grades1     3dbms=excel2000     outfile='c:\Myfiles\Grades.xls'; 

Identify a particular spreadsheet to write to in a workbook.

 sheet=Grades1;  run; 

Example 4: Exporting a Microsoft Access Table

Procedure features:

• PROC EXPORT statement arguments:

  • DATA=

  • DBMS=

  • OUTTABLE=

  • REPLACE

• Data Source Statement:

  • DATABASE=

This example exports a SAS data set named SASUSER.CUST, the first five observations of which follow, and creates a Microsoft Access 97 table. The security level for this Access table is none, so it is not necessary to specify any of the database security statements.

 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 

Program

Identify the input SAS data set, specify the output DBMS table name and the output data source, and overwrite the output file if it exists. The output file is a Microsoft Access 97 table. The option REPLACE overwrites an existing file. If you do not specify REPLACE, PROC EXPORT does not overwrite an existing file.

 proc export data=sasuser.cust     outtable="customers"     dbms=access97     replace; 

Specify the path and filename of the database to contain the table.

 database="c:\myfiles\mydatabase.mdb";  run; 

Example 5: Exporting a Specific Spreadsheet in an Excel Workbook on a PC Server

Procedure features:

• PROC EXPORT statement arguments:

  • DATA=

  • DBMS=

  • OUTFILE=

• Data Source Statement:

  • SHEET=

  • SERVER=

  • PORT=

  • VERSION=

This example exports a SAS data set named SASHELP.CLASS and creates an Excel 2000 workbook named demo.xls. SASHELP.CLASS becomes one spreadsheet named Class in the workbook named demo.xls.

Program

 proc export data=sashelp.class      dbms=excelcs      outfile='c:\Myfiles\demo.xls';      sheet='Class';      server='sales';      port= 4632;      version='2000';  run;