|

Getting Excel Data into SAS Studio: Beyond the Defaults

A while back, I wrote a post on getting your Excel data into SAS Studio the quick and easy way. However,  I hear you saying,

What about ME? What about MY needs? What if I don’t want my data written to the working directory? What if my file has the names at the top and I want to keep those names?

First of all, open a program file and run some code that assigns the LIBNAME to the directory where you want your data stored. It should look like this but whatever is in the quotation marks should be where your data are stored.

LIBNAME mydata “/courses/d1234566789” ;
run;

Second, upload your Excel File

sasexcel1

Under FILES, select the folder where you would like your data stored. Click on the UPLOAD FILES button (the arrow pointing up at the top of the screen) and then click CHOOSE FILES to go to where the file is stored on your computer. Select that file, click the button on the pop-up window that says UPLOAD. Now you have your Excel file, uploaded but you want a SAS file.sasExcel2

Go under TASKS and UTILITIES and click the arrow to select UTILITIES and then select IMPORT DATA.

 

sasExcel3

On the right, you’ll see this big window that says DRAG AND DROP YOUR FILE HERE.

file list

In the left pane, open the FILES directory and go to where you saved your Excel file. Drag it into the window. Once you’ve done that, this wi If you stopped here, you would have the file written to the working directory, and named import.

import option

If you want to change that, click the button that says CHANGE.

changing default name in boxes

This pops up. Don’t see the directory you want? Did you run the LIBNAME statement at the very beginning of this post to assign a library reference to that directory? For shame! You think I just make this stuff up? Go back and do it now.

Okay, should you be concerned that your library name is greyed out? No, you should not. That just means you cannot change the name of your library reference here. If you wanted to change that library name from “mydata” to “yourdata” you’d have to do it in the LIBNAME statement.

Type the name you want for the data set. Do not forget to click SAVE or you may as well have skipped this step.

Click the little running guy at the top of the window.

Before you go, notice that SAS also generates code for you. If, like me, you anticipate that your data may change and you may need to do this again, you can copy and paste the code generated by SAS and save it in a program file. Run it again to recreate your data set. How likely is that to happen?  Well, it happened to me today when I inadvertently (that’s a synonym for “stupidly”, right?) wrote over this exact data set.

/* Generated Code (IMPORT) */
/* Source File: az_pretest.xlsx */
/* Source Path: /home/annmaria.demars/data_analysis_examples/data2017 */
/* Code generated on: 7/31/17, 6:09 PM */

%web_drop_table(MYDATA.aztech_pre);
FILENAME REFFILE ‘/home/annmaria.demars/data_analysis_examples/data2017/az_pretest.xlsx’;

PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=MYDATA.aztech_pre;
GETNAMES=YES;
RUN;

PROC CONTENTS DATA=MYDATA.aztech_pre; RUN;
%web_open_table(MYDATA.aztech_pre);
run;

Okay, there you go. With a few clicks, your Excel file is accessible in SAS Studio as a SAS data set and you have a copy of the code that did it.

Next post we’ll start whipping that data into shape.

When I am not writing about SAS, I’m making games that teach math, social studies and language.

Check them out.

screen shots from our games

 

 

Similar Posts

4 Comments

  1. Hi Dr. De Mars! Before drawing attention to your post on the SAS Analytics U Community, I want to see if there are words missing from the opening paragragh, where the sentence ends like so:

    “What if my file has the names at the top and I want to keep those names instead of…”?

  2. Thanks. I changed that sentence to be “What if my file has the names at the top and I want to keep those names?”
    Sorry for the delayed response. I was all over Wyoming last week. Beautiful place but internet access isn’t always the best.

Leave a Reply

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