BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AJS1
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Panagiotis
SAS Employee

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

View solution in original post

8 REPLIES 8
Panagiotis
SAS Employee

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

AJS1
Obsidian | Level 7

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:

 

ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File MYDATA.'2014'n.DATA does not exist.
ERROR 22-322: Syntax error, expecting one of the following: ;, (, CENTILES, DATA, DETAILS, DIR, DIRECTORY, FMTLEN, LIB, MEMTYPE,
MT, MTYPE, NODETAILS, NODS, NOPRINT, ORDER, OUT, OUT2, SHORT, VARNUM.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
Panagiotis
SAS Employee

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

AJS1
Obsidian | Level 7

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;   

Panagiotis
SAS Employee

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.

 

  • One is to name your worksheets following SAS table name rules.
  • Another would be to use "2014 counties"n as a table name when referring to it in SAS like I showed you. You would use whatever works best for you. You would need to use this if your worksheet names do not follow SAS table naming conventions. I prefer to not have to use "<table name>"n so i'll name my worksheets accordingly.

 

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

AJS1
Obsidian | Level 7

Wonderful advice.  Thank you!

Reeza
Super User
SAS UE includes the licenses to access and create Excel data.
Cynthia_sas
SAS Super FREQ

...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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 949 views
  • 4 likes
  • 4 in conversation