We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Creating a SAS table from a Microsoft Excel file

by SAS Employee jennifers_sas on ‎07-18-2014 09:11 AM - edited on ‎10-05-2015 03:25 PM by Community Manager (1,333 Views)

To create a SAS table from data in a Microsoft Excel file, you must save the Microsoft Excel file in a shared folder.  By default, your shared folder is /folders/myfolders. You might have additional shared folders, depending on your needs.

  1. In SAS Studio, open the Snippets section.
  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 below.

 

csv_code_example.gif

 

Line 1: 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 additional shared folders, an example filename is folders/myshortcuts/folder1/Sales.xlsx.

Lines 2: 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 between sessions, specify a library name for a shared folder.

Line 3: The DBMS statement specifies that you are importing an Excel file.

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

Line 5: End your program with the RUN statement.

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

 

Additional topics

Comments
by TonyFly
on ‎07-26-2014 12:05 PM

Hello .I am using SAS univeristy edittion and I am trying to import data from excel.

There is the error message :

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 proc import datafile = "/folders/myfolders/SAS001/data/intro_auto.xlsx"

44 out=auto_excel

45 dbms=excel replace;

ERROR: DBMS type EXCEL not valid for import.

by ingrid
on ‎08-01-2014 06:01 PM

I can see the csv file in the myfolders, but whne import it, it said "no physical file", get confused?!

/** FOR CSV Files uploaded from Windows **/

50         FILENAME CSV "Folders/MyFolders/deleteme.csv" TERMSTR=CRLF;

51        

52        

53         /** Import the CSV file.  **/

54        

55         PROC IMPORT DATAFILE=CSV

56             OUT=WORK.MYCSV

57             DBMS=CSV

58             REPLACE;

59         RUN;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

WARNING: Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/Folders/MyFolders/deleteme.csv.

ERROR: Import unsuccessful.  See SAS Log for details.

Your turn
Sign In!

Want to write an article? Sign in with your profile.