I wanted to try using some of the analytics I learned in lessons 1 through 3 with my own data - do I need a license to access Excel data when using SAS University Edition/SAS Studio?
Hello @AJS1 ,
You should be able to read Excel files when using University Edition. Do you have an error you can post? The key is to put the Excel file in the in the location where your PG1 data is. That is the location the virtual box knows about.
Most likely a folder somewhere that ends like this 'VirtualBox VMs\SAS University Edition 1\myfolders'.
- Peter
Hello @AJS1 ,
You should be able to read Excel files when using University Edition. Do you have an error you can post? The key is to put the Excel file in the in the location where your PG1 data is. That is the location the virtual box knows about.
Most likely a folder somewhere that ends like this 'VirtualBox VMs\SAS University Edition 1\myfolders'.
- Peter
I was able to create a libname but get errors with my proc contents step (note: my worksheets within my animal data workbook are named as "2014 Counties"):
options validvarname=v7;
libname mydata xlsx "/folders/myfolders/sasuser.v94/animaldata.xlsx";
proc contents data=mydata.2014 counties;
run;
Errors:
Is the worksheet name "2014 counties". With a space?
For a quick look it looks like you are telling SAS to look for data=2014 counties. That space is critical. SAS is looking for a worksheet only called '2014', then trying to use counties as an option.Which is causing the both errors. Can't find the sheet, can't use counties as an option.
What you need to do is tell SAS that there is a space in the sheet name. To do that place the sheet name within quotes with an n at the end. For example:
proc contents data=mydata.'2014 counties'n;
run;
- Peter
Yes, there is a space. I used your code and it worked! If I rename my worksheets (prior to making a libname) so that there are no spaces can I use:
proc contents data=mydata.2014 counties;
run;
Yes, but you do have to follow naming rules in SAS. In Excel there are really no naming rules.
Off the top of my head SAS tables must:
- Start with a letter or underscore (your sheet 2014 counties does not)
- Not contain any symbols or spaces (yours does)
- Max of 32 characters I think
Multiple was to address this issue.
So for your specific example. Name the Excel worksheet Counties2014 (or anything that follows SAS naming rules like _2014Counties, _Counties2014, Counties_2014, etc). Then when you use PROC CONTENTS you can use:
proc contents data=mydata.Counties2014;
run;
This will work without the "<table name>"n because the Excel worksheet name follows SAS naming rules for tables.
Hope that's clear. Let me know if you have any more questions.
- Peter
Wonderful advice. Thank you!
...and, in addition to the other suggestions, our Programming 1 class shows several examples of reading and writing Excel files. This will work with SAS University Edition and SAS OnDemand for Academics. With a local or server install of SAS at your site, you'll need to be running 9.4 Maintenance Release 2 or higher to use the XLSX Libname engine that we show in Programming 1.
Cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Follow along as SAS technical trainer Dominique Weatherspoon expertly answers all your questions about SAS Libraries.
Find more tutorials on the SAS Users YouTube channel.