topic Re: Basic analytics - practicing using syntax with my own data (Excel) in Programming 1 and 2

Basic analytics - practicing using syntax with my own data (Excel)

AJS1 — Thu, 09 Jan 2020 15:10:03 GMT

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?

Re: Basic analytics - practicing using syntax with my own data (Excel)

Panagiotis — Thu, 09 Jan 2020 15:47:59 GMT

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

Re: Basic analytics - practicing using syntax with my own data (Excel)

Reeza — Thu, 09 Jan 2020 16:30:11 GMT

SAS UE includes the licenses to access and create Excel data.

Re: Basic analytics - practicing using syntax with my own data (Excel)

Cynthia_sas — Fri, 10 Jan 2020 14:39:36 GMT

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

Re: Basic analytics - practicing using syntax with my own data (Excel)

AJS1 — Fri, 10 Jan 2020 15:58:44 GMT

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.

Re: Basic analytics - practicing using syntax with my own data (Excel)

Panagiotis — Fri, 10 Jan 2020 16:28:20 GMT

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

Re: Basic analytics - practicing using syntax with my own data (Excel)

AJS1 — Fri, 10 Jan 2020 16:38:16 GMT

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;

Re: Basic analytics - practicing using syntax with my own data (Excel)

Panagiotis — Fri, 10 Jan 2020 16:47:09 GMT

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

Re: Basic analytics - practicing using syntax with my own data (Excel)

AJS1 — Fri, 10 Jan 2020 17:00:19 GMT

Wonderful advice. Thank you!