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.
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.
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.
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.
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 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;
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.
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.
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
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.