topic Re: can't convert an excel file to a sas data set in SAS Studio

can't convert an excel file to a sas data set

jkl123 — Wed, 26 Aug 2020 04:30:09 GMT

Hello there,

I am a new user of SAS and now I have a big trouble with converting an excel file to a sas data set. I tried sas studio and 9.4, but not succeed either. For example, I created an excel by hand, called data.xlsx, located at desk.There are 2 worksheet in it, one called tests.xlsx, like the following, and the other called family.xlsx.

ID	Name	RestHR	MaxHR	RecHR	TimeMin	TimeSec	Tolerance	TestDate
2458	Murray, W	72	185	128	12	38	D	8/25/2008
2462	Almers, C	68	171	133	10	5	I	6/26/2008
2501	Bonaventure, T	78	177	139	11	13	I	6/26/2008

Here is my code with studio:

libname results xlsx 'C:\Users\linsq\OneDrive\Desktop/data.xlsx';
data stress;
set results.'tests$'n;
run;

However, log shows:

NOTE: Libref RESULTS was successfully assigned as follows:

ERROR: File RESULTS.'tests$'n.DATA does not exist.

With SAS9.4, log shows :

ERROR: The XLSX engine cannot be found.

ERROR: Error in the LIBNAME statement.

Any suggestions ? Any comments? I really appreciate.

Re: can't convert an excel file to a sas data set

andreas_lds — Wed, 26 Aug 2020 05:04:52 GMT

Use proc productstatus to verify that access to pcfiles is installed and, if it is not, use proc setinit to verify that you have licenced it.

Re: can't convert an excel file to a sas data set

Kurt_Bremser — Wed, 26 Aug 2020 05:36:28 GMT

So your (local?) SAS 9.4 does not have ACCESS to PC Files licensed or installed.

The use of SAS Studio points to a remote BI server, or University Edition. Which is it? BTW, both of those do not have access to your C: drive.

Re: can't convert an excel file to a sas data set

jkl123 — Wed, 26 Aug 2020 15:04:21 GMT

Hi, andreas_Ids, thank you.

I tried your suggestion, and my log shows:

For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M1

does 9.4_M1 mean it cannot access to PC files? I also tried proc setinit;
yes, it's expired.

Re: can't convert an excel file to a sas data set

Kurt_Bremser — Wed, 26 Aug 2020 15:13:35 GMT

Is only the the expiration date for ACCESS to PC Files in the past, and everything else in the future?

This could be the result of temporary licensing for testing.

Check with the people in your organization if that was an oversight; you will need an updated license, and while you (or your SAS admin) are at it, also get a current version (9.4 M7 at the moment) and upgrade to that. M1 misses a lot of goodies that were added in the meantime.

Re: can't convert an excel file to a sas data set

jkl123 — Wed, 26 Aug 2020 15:27:32 GMT

Hello, Kurbremser,

My studio is University edition. I moved my data.xlsx to C:\ and then modified my codes like the following:

libname results xlsx 'C:\data.xlsx';

data stress;
set results.'tests$'n;
run;

But the same issue happened. I am not sure whether this is what you mean or not. Thanks.

KurtBremser

Re: can't convert an excel file to a sas data set

jkl123 — Wed, 26 Aug 2020 15:33:21 GMT

Hi, KurtBremser ,

everything expired. I installed sas software when I was in my former
organization, but I left there a couple of years ago. Recently I stayed at
home, thinking about learning it again. Is there any way that I can update
it privately? Thanks.

Re: can't convert an excel file to a sas data set

Kurt_Bremser — Wed, 26 Aug 2020 15:40:56 GMT

Funny that SAS starts up at all.

Use University Edition. But there, you have to place your Excel file in the shared folder, and use the internal path /folders/myfolders to correctly access it.

Re: can't convert an excel file to a sas data set

Tom — Wed, 26 Aug 2020 15:46:15 GMT

@jkl123 wrote:
Hi, KurtBremser ,

everything expired. I installed sas software when I was in my former
organization, but I left there a couple of years ago. Recently I stayed at
home, thinking about learning it again. Is there any way that I can update
it privately? Thanks.

You can install the SAS/University Edition for use in learning SAS. Note that it will install into a virtual machine that will have access to only one folder on your real machine. So once you have it working to use it for this problem you will need to move the XLSX file into that folder on your real machine and reference it in the SAS code using the name that accesses that folder on the virtual machine. So if you XLSX file is named myfile.xlsx then code to make a libref pointing to it and copy all of the sheets into work datasets would look like:

libname mylib xlsx '/folders/myfolders/myfile.xlsx';
proc copy inlib=mylib outlib=work;
run;

Re: can't convert an excel file to a sas data set

jkl123 — Wed, 26 Aug 2020 15:54:21 GMT

I uploaded data.xlsx to sas studio, and I did succeed with PROC IMPORT, but
still got trouble with the following.

libname results xlsx '/folders/myfolders/data.xlsx';
data stress;
set results.'tests$'n;
run;

However, I modified the above codes a little bit, like the following:

libname results xlsx '/folders/myfolders/data.xlsx';
data stress2;
set results.tests;
run;

I am successful. It's a surprise. I thought when reading worksheets, I need
to use 'worksheet$'n.

Re: can't convert an excel file to a sas data set

jkl123 — Wed, 26 Aug 2020 16:29:21 GMT

Hi, Tom ,

Thanks. I now understand what you are saying and I also tried your way. It
works good for those worksheets with variable names in the first row. But
for those sheets without variable names, this way converted my first
observation to variables. Any suggestions on this question? Thanks.

Re: can't convert an excel file to a sas data set

Cynthia_sas — Fri, 28 Aug 2020 16:13:17 GMT

Hi:
If you are using SAS University Edition in a Virtual Machine, you cannot use a C drive location for your files. As part of the Virtual Machine setup process, you created a shared folder in the Virtual Machine management screens. You need to put your XLSX file in THAT location. The shared folder path on Windows should be something like:
c:\folder\subfolder\SASUniversityEdition\myfolders or
c:\SASUniversityEdition\myfolders or
C:\users\xyzxyz\SASUniversityEdition\myfolders

It doesn't matter what the high level part of the path is. The main folders should be as shown in green above. There should be a SASUniversityEdition folder on your system with a sub-folder called myfolders. THAT is the location you needed to define as your shared folder location. So, let's assume that you installed and defined THIS as your shared folder (less typing directly under C:) -- and put your Excel file there:
c:\SASUniversityEdition\myfolders\myworkbook.xlsx
Then, the reference you would use in SAS Studio would be:
libname xlwkbk xlsx '/folders/myfolders/myworkbook.xlsx';

You must use the /folders/myfolders reference in University Edition because the Virtual Machine is delivered on a Linux platform and you can't use Windows folder names with SAS in the Virtual Machine. You must use the Linux/Unix folder name of the shared folder location.

You should be able to see the XLSX file in your SAS Studio navigation for Server Files and Folders on the left side of the Studio window. If you don't see your XLSX file in Server Files and Folders, then you may have put it into a subfolder or you may need to move it to that location. When you can see the XLSX file in Server Files and Folders, you can right-click on it and choose Properties. The Location field in the Properties window will show you the EXACT path to use in your LIBNAME statement. Then you can continue on from there.

Hope this helps,
Cynthia

Re: can't convert an excel file to a sas data set

jkl123 — Fri, 28 Aug 2020 14:35:51 GMT

Hello, Cynthia,

Thank you for your explanation. I am now clear. I tried the code in the
following:

libname carinfo xlsx '/folders/myfolders/auto.xlsx';
data car;
set carinfo.'auto$'n;
run;

But the log shows
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File CARINFO.'auto$'n.DATA does not exist.

But when I used
set carinfo.auto;
removing '$'n, this program runs correctly. Obviously it contradicts what
I learned from books. Any explanation on this issue? Thanks.

Re: can't convert an excel file to a sas data set

Tom — Fri, 28 Aug 2020 15:05:58 GMT

You need to include an actual range, or at least the upper left cell of the range.

Example:

data test1;
  set x.sheet1;
run;

data test2;
  set x.'sheet1$A1:'n;
run;

proc compare data=test1 compare=test2;
run;

Re: can't convert an excel file to a sas data set

jkl123 — Fri, 28 Aug 2020 15:16:51 GMT

Hi, Tom,

Thank you so much. Now I am fully clear at this problem. I really
appreciate everyone's help. Thanks again.

Re: can't convert an excel file to a sas data set

Cynthia_sas — Sat, 29 Aug 2020 15:57:38 GMT

Hi:
Different engines required different reference methods for worksheets. The named literal method that you originally tried is NOT needed with the XLSX engine, but was used with other engines. Once you issue your LIBNAME statement, like this:
libname carinfo xlsx '/folders/myfolders/auto.xlsx';

You can always find out the correct sheet names to use by doing this:
proc contents data=carinfo._all_ nods;
title 'What are sheet names';
run;
title;

Use the name form that you see in the list of tables in the PROC CONTENTS output. My guess is that if you run PROC CONTENTS on your workbook, you will see AUTO listed as a table without the " 'n" named literal form.

Cynthia

Re: can't convert an excel file to a sas data set

jkl123 — Sat, 29 Aug 2020 16:57:51 GMT

Hello, Cynthia,

Yes, you are correct. I tried and the spreadsheet name just showed up.
Thanks a million.