• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Analysis Factor

The Analysis Factor

Statistical Consulting, Resources, and Statistics Workshops for Researchers

  • Home
  • Our Programs
    • Membership
    • Online Workshops
    • Free Webinars
    • Consulting Services
  • About
    • Our Team
    • Our Core Values
    • Our Privacy Policy
    • Employment
    • Collaborate with Us
  • Statistical Resources
  • Contact
  • Blog
  • Login

The Secret to Importing Excel Spreadsheets into SAS

by Audrey Schnell 1 Comment

My poor colleague was pulling her hair out in frustration today.

You know when you’re trying to do something quickly, and it’s supposed to be easy, only it’s not? And you try every solution you can think of and it still doesn’t work?

And even in the great age of the Internet, which is supposed to know all the things you don’t, you still can’t find the answer anywhere?

Cue hair-pulling.

Here’s what happened: She was trying to import an Excel spreadsheet into SAS, and it didn’t work.

Instead she got:

Look familiar? If you’re like my colleague, you’re wondering, what the blank is going on here?

Well, if you have SAS 64-bit and Office 32-bit (or even Office 64-bit), you’ll find that the 64-bit version of SAS does not have the interface to communicate with Office and therefore cannot import spreadsheets.

Yep, you read that right: it can’t do it through the wizard and it can’t do it through Proc Import.

So here’s what you have to do.

Save the Excel spreadsheet as a .csv file, and then import it. (You can only have 1 worksheet in the .csv file, but other than that, you shouldn’t see any differences from an Excel spreadsheet.)

It should look like this:

PROC IMPORT OUT= WORK.QA *desired filename;

            DATAFILE= “C:\file to import”

            DBMS=CSV REPLACE; guessingrows=1000;

     GETNAMES=YES;

     DATAROW=2;

RUN;

BTW, the guessingrows option is very useful: it tells SAS to read through the specified number of lines (the limit is 214,7483,647 and the default is 20) to determine the length of variables.

Without this option, if the first 20 values are 2 characters and the 21st is 3 characters, your values will be truncated. Specifying the guessingrows (you can just use guessingrows=Max) ensures that SAS looks at all the values in your dataset (i.e., all rows) before it sets the length of variables.

So spread the word. And save your hair.

Life will be easier if everyone starts using .csv files instead of Excel files.

The Pathway: Steps for Staying Out of the Weeds in Any Data Analysis
Get the road map for your data analysis before you begin. Learn how to make any statistical modeling – ANOVA, Linear Regression, Poisson Regression, Multilevel Model – straightforward and more efficient.

Tagged With: excel, guessingrows, importing data, proc import, SAS

Related Posts

  • Multiple Imputation in a Nutshell
  • Statistical Software Access From Home
  • Member Training: What’s the Best Statistical Package for You?
  • Ten Ways Learning a Statistical Software Package is Like Learning a New Language

Reader Interactions

Comments

  1. Bill Price says

    October 22, 2021 at 12:31 pm

    I’ve found the code template below to work fine with Excel files in 64 bit SAS without converting to CSV, which can be cumbersome when multiple files are involved. This still does not allow reformatting of character ‘NA’ to missing numeric, however, like delimited files can do.

    PROC IMPORT OUT= WORK.name
    DATAFILE= “path\filename.xlsx”
    DBMS=XLSX REPLACE;
    sheet=”sheet name”;
    RUN;

    This also works with *.xls with by changing the filename extensions.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Please note that, due to the large number of comments submitted, any questions on problems related to a personal study/project will not be answered. We suggest joining Statistically Speaking, where you have access to a private forum and more resources 24/7.

Primary Sidebar

This Month’s Statistically Speaking Live Training

  • Member Training: Analyzing Pre-Post Data

Upcoming Free Webinars

Poisson and Negative Binomial Regression Models for Count Data

Upcoming Workshops

  • Analyzing Count Data: Poisson, Negative Binomial, and Other Essential Models (Jul 2022)
  • Introduction to Generalized Linear Mixed Models (Jul 2022)

Copyright © 2008–2022 The Analysis Factor, LLC. All rights reserved.
877-272-8096   Contact Us

The Analysis Factor uses cookies to ensure that we give you the best experience of our website. If you continue we assume that you consent to receive cookies on all websites from The Analysis Factor.
Continue Privacy Policy
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT