Resources

SAS University Edition: Help Center


How do I import data from a raw data file, a Microsoft Excel worksheet, or a DBMS file?

Before you can import data, make sure that you can access the data file from SAS Studio. You can import data with the Import Tool in SAS Studio. SAS Studio also includes snippets for importing a raw data file, such as a file with comma-separated values, as well as a Microsoft Excel worksheet.
bullet icon Show instructions for: Import Tool | Snippets

Before you can import the data, you must be able to access the data file from SAS Studio.

For more information about importing a raw data file, a Microsoft Excel worksheet, or a DBMS file, see the Importing Data topic in SAS Studio: User's Guide.

Before you can import the data, you must be able to access the data file from SAS Studio.

SAS Studio contains these snippets that you can use to import your data:

  • To import a raw data file (such as a file with comma-separated values), use the Import the CSV File snippet in SAS Studio. The snippet uses the IMPORT procedure in SAS. By changing the values of the DATAFILE= and DBMS= options, you can customize this code to import other types of raw data files. For more information about the DATAFILE= and DBMS= options, see the documentation for the IMPORT procedure in Base SAS Procedures Guide.

  • To import a Microsoft Excel worksheet, use the Import XLSX File snippet.

    1. In SAS Studio, open the Snippets section in the navigation pane.

    2. Select Snippets > Data > Import XLSX File.

      The following code is added to the Code tab. You need to customize the filenames in this code. Each line of code is explained.

      Example Code of Creating a SAS Table from an Excel Worksheet

      Line 3: In the PROC IMPORT statement, specify the name of your Excel file. This file must be saved in one of your shared folders. If your Excel file is saved in My Folders, an example filename is /folders/myfolders/Sales.xlsx. If you have created folder shortcuts, an example filename is /folders/myshortcuts/folder1/Sales.xlsx.

      Note: You must include the forward slash at the beginning of the directory path. If you omit this forward slash, you will see a Physical file does not exist error in the log.

      Line 4: Use the OUT statement to specify where to save the SAS table in the format Library.Filename. In this example, the SAS table is a file called MYEXCEL and is saved to the Work library, which is a temporary location. If you want this data to persist, specify a library name for a shared folder.

      Line 5: The DBMS statement specifies that you are importing an Excel file. By default, the XLSX file extension is used.

      Tip: If you are importing data from an older version of Microsoft Excel, change XLSX to XLS. To create a snippet for importing your XLS files, see How do I create a snippet for importing XLS files?

      Line 6: The REPLACE statement specifies to overwrite any existing file with the same name.

      Line 7: End your program with the RUN statement.

      You can use the PROC PRINT statement to view the new SAS table in SAS Studio.