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

[jkl123]

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.

[andreas_lds · Accepted solution]

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.

[jkl123]

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.

[Kurt_Bremser]

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.

[jkl123]

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.

[Kurt_Bremser]

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.

[jkl123]

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.

[Tom]

---

@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;

[jkl123]

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.

[Kurt_Bremser]

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.

[jkl123]

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

[Cynthia_sas]

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

[jkl123]

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.

[Tom]

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;

[jkl123]

Hi, Tom,

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