How to read Excel spreadsheets with SAS University Edition

8

This blog post teaches you how to import an Excel spreadsheet into the free SAS University Edition, so you can further analyze and graph the data.

First, you need to create a folder on your local computer (such as C:\\SASUniversityEdition\\myfolders\\ ), and then then set that up as a Shared Folder so that Oracle VM VirtualBox can see it. SAS Support has set up a help page describing how to do that. Once you've got the shared folder set up, you can access it via the path /folders/myfolders/ in your SAS job (fyi - the emulator is running a flavor of Unix, therefore this is a Unix path).

Now when you place files (such as Excel spreadsheets) into this folder on your local computer, your SAS jobs (running in the Virtualbox emulator) will be able to see them. So let's download an interesting spreadsheet from the Web, and save it into that location...

The price of gasoline (petrol) here in the US has always been of interest to me. Possibly because I used to have a Ford F250 truck with a 6.8 liter V10 engine that got 8mpg in city driving (I now have a Prius! LOL)

f250

Therefore I'm glad the US Energy Information Administration tracks the average price for a gallon of gasoline in the US, and makes it available on their Web site. Use your favorite browser on your local computer, and go to their page and click the Download Data (XLS File) link, located near the top of the page. Save the spreadsheet on your local computer into C:\\SASUniversityEdition\\myfolders\\EMM_EPMR_PTE_NUS_DPGw.xls

Now you can run the following code in your SAS University Edition to import the spreadsheet data into a SAS dataset:

PROC IMPORT DBMS=xls OUT=eia (rename=(a=date b=price)) replace
DATAFILE="/folders/myfolders/EMM_EPMR_PTE_NUS_DPGw.xls";
RANGE="Data 1$A4:B1500";
GETNAMES=NO;
RUN;

You can click the dataset in Folders->My Libraries->WORK->EIA and view the table.

eia_data

But viewing the raw numbers only provides a certain amount of insight. If we could graph the data, then we could much more easily look for trends and such. Luckily you've got SAS University Edition - you can run the following code to create a graph of the data from year 2000 to present. Notice that I'm adding several 'extras' here, to show you the syntax for subsetting the data, adding custom labels to the axes, and formatting the price as US $.

proc sgplot data=eia (where=(date GE '01jan2000'd));
label date='Year' price='US Gasoline Price per Gallon';
series x=date y=price;
yaxis valuesformat=dollar10.2;
run;

I'm going to let you tell me what you see in the graph (in comments)! Can you identify any major events based on abrupt changes in the graph? Do any trends or cycles jump out at you? What are your theories on gasoline prices? :)

gas_prices

 

 

Share

About Author

Robert Allison

The Graph Guy!

Robert has worked at SAS for over 20 years, and is perhaps the foremost expert in creating custom graphs using SAS/GRAPH. His educational background is in Computer Science, and he holds a BS, MS, and PhD from NC State University. He is the author of several conference papers, has won a few graphic competitions, and has written a book (SAS/GRAPH: Beyond the Basics).

8 Comments

  1. Robert, would you be able to share any advice on how to import EXCEL workbooks with multiple worksheets (either with SAS University Edition or Desktop Base SAS)? Thank you!

    • Robert Allison
      Robert Allison on

      Proc Import in SAS University Edition uses Unix SAS, and therefore can only read simple Excel spreadsheets (dbms=xls). SAS running on Windows can use dbms=excel which uses Microsoft Jet to read the Excel spreadsheets, which can read more complex spreadsheets ... but I'm not 100% sure of the details when it comes to multiple workbooks. I'd recommend calling SAS Tech Support on this one (they've got a whole SAS/Access group, and I bet they can provide you with a definitive answer!)

  2. Pingback: How to turn rows into columns in SAS University Edition - The SAS Training Post

    • Robert Allison
      Robert Allison on

      Good question Susan! I am not 100% sure, but I do not think you can use the Excel Engine libname in this scenario.

  3. hi please guide me how to solve the following error while uploading excel file

    ERROR: FILE= or TABLE= is required and must be specified.

    • Robert Allison
      Robert Allison on

      Hmm - we'd need a little more information to diagnose this problem, I believe.

      I also invite you to post the question (with more details) as a discussion topic on the SAS Analytics U "community" page. This way the whole user community can pitch in and give tips & advice, based on their experience and knowledge (the question will get much more exposure to possible experts there):

      https://communities.sas.com/community/sas-analytics-u

Leave A Reply

Back to Top