I used the code below to read an external Excel file and just noticed it only read the first 255 columns. When I used PROC IMPORT, all 266 columns were read in. Some of the column names are longer than 32 characters, so PROC IMPORT gave some variable names as VAR15, etc. When I used the code below, it did not assign names such as this but instead, it would read the first 32 characters and truncate the rest, which makes renaming a bit easier.
Is this expected? I'm running SAS 9.4 on a PC.
libname datain 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
data data1;
set datain.'1936-3701-SPF-v1$'n;
run;
I have to give it a try.
WOW G. I know what is going on . You must misspell the path name or filename .
When I fix path name from /folders/myfolder/x.xlsx ---> /folders/myfolderS/x.xlsx
I get the result . But when I use WRONG path name ,SAS still show Libname has been assign successfully.It is so weird. That might be a BUG for SAS.
libname X XLSX '/folders/myfolders/x.xlsx' ;
proc sql;
select nliteral(memname)
from dictionary.members
where libname='X' ;
quit;
Notes (2)
53
54 libname X XLSX '/folders/myfolders/x.xlsx' ;
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: /folders/myfolders/x.xlsx
55 proc sql;
56 select nliteral(memname)
57 from dictionary.members
58 where libname='X' ;
59 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.03 seconds
60
61
62
63
64 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
74
SHEET1 |
SHEET2 |
SHEET3 |
And remove $ from your table name ,that could work.
60 data data1;
61 set x.'Sheet1'n;
62 run;
NOTE: The import data set has 1 observations and 2 variables.
NOTE: There were 1 observations read from the data set X.Sheet1.
NOTE: The data set WORK.DATA1 has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
Xia Keshan
Message was edited by: xia keshan
Message was edited by: xia keshan
I would try the XLSX LIBNAME option which became available in 9.4 M2 to read more than 256 columns.
SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition
libname datain xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
32 characters is the maximum length for SAS variable names, hence the truncation / rename behaviour is expected.
BTW the SAS option VALIDVARNAME controls whether Excel column names are converted to valid SAS column names or not. Use VALIDVARNAME = V7 to translate, ANY for no translation.
Not sure what's wrong. Here's my code and the ERROR statement in the log. When I use this data step with the libname statement from my original post, everything works. ...hmmm
libname datain xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
data data1;
set datain.'1936-3701-SPF-v1$'n;
run;
28 libname datain xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
NOTE: Libref DATAIN was successfully assigned as follows:
Engine: XLSX
Physical Name: G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx
29 data data1;
30 set datain.'1936-3701-SPF-v1$'n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File DATAIN.'1936-3701-SPF-v1$'n.DATA does not exist.
31 run;
Try looking in the library DATAIN and see what SAS reports as the sheet names, then use what SAS is showing.
If you are using EG just look in the Server List. In Display Manager the SAS Explorer.
I went to explorer and I see the SAS set with that name. When I try to open it, I get a message that says "Multiple SAS files with this name exist. The table named Datain."1936-3701-SPF-V1"n will be opened. When I click 'OK' I get a message "ERROR: Access of sequential tables is not allowed in this viewer. Datain."1936-3701-SPF-V1"n cannot be opened. Do you want to select a different table to open?" When I click 'yes' I get nothing.
Use DICTIONARY to check the real table name in EXCEL. Maybe what you saw is not really what they are.
proc sql;
select nliteral(memname)
from dictionary.members
where libname='DATAIN' ;
quit;
Ok, in an effort to simplify matters, I renamed the tab in the Excel file = Sheet1.
Code that works:
libname datain 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
data data2;
set datain.'Sheet1$'n;
run;
Code that does not work:
libname datain xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
data data1;
set datain.'Sheet1$'n;
run;
Log from code that does not work:
1 libname datain xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
NOTE: Libref DATAIN was successfully assigned as follows:
Engine: XLSX
Physical Name: G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx
2 data data1;
3 set datain.'Sheet1$'n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File DATAIN.'Sheet1$'n.DATA does not exist.
4 run;
I have found on our system that a LIBNAME reference to an Excel file needs to be cleared before I can use it again.
Try adding LIBNAME DATAIN CLEAR ; RUN ;
If that clears the error, you may be able to use the other suggestions to read the tab without having to rename it.
Hope that helps...
Wendy
libname datain 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
data data2;
set datain.'Sheet1$'n;
run;
libname datain clear ;
run ;
libname datain xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';
data data1;
set datain.'Sheet1$'n;
run;
libname datain clear ;
run ;
Thanks for the tip, Wendy, but I got the same error. I even closed the SAS session and opened a new one and tried it with another Excel file but I get the error.
6 libname a xlsx 'G:\Departments\Research\test scores\Aspire\2015\proc contents.xlsx';
NOTE: Libref A was successfully assigned as follows:
Engine: XLSX
Physical Name: G:\Departments\Research\test scores\Aspire\2015\proc contents.xlsx
7 data one;
8 set a.'Sheet1$'n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File A.'Sheet1$'n.DATA does not exist.
9 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ONE may be incomplete. When this step was stopped there were 0
observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.03 seconds
10 LIBNAME a CLEAR ;
NOTE: Libref A has been deassigned.
10 ! RUN ;
Try this so you are not explicitly naming the sheets.
libname a xlsx 'G:\Departments\Research\test scores\Aspire\2015\proc contents.xlsx';
proc datasets library = a;
copy in = a out = work;
run;
quit;
I know this has been said many times before, but why do you need to use Excel in the first place. It is not a good format for the storing or transport of data. As the above posters have mentioned, special characters, mixes of data, unstructured format etc. all go towards making it one of the worse tools to do anything (other than pivot tables) with data. Get your data into CSV (or XML or simliar). You can do this in a number of ways, my suggestion is to go back to the datasource and get them to change their export - reason, when the data is mis-interpreted by Excel, which it will be, then its their problem. You can do it yourself though with SaveAs in Excel, and choose CSV. Then write a datastep import program to read the data in properly. If you have multiple sheets then a simple VBA macro will suffice, something like:
Sub tmp()
i = 1
For Each s In Workbook.Sheets
s.SaveAs Filename:="Sheet" + i, FileFormat:=xlCSV
i = i + 1
Next
End Sub
I have to give it a try.
WOW G. I know what is going on . You must misspell the path name or filename .
When I fix path name from /folders/myfolder/x.xlsx ---> /folders/myfolderS/x.xlsx
I get the result . But when I use WRONG path name ,SAS still show Libname has been assign successfully.It is so weird. That might be a BUG for SAS.
libname X XLSX '/folders/myfolders/x.xlsx' ;
proc sql;
select nliteral(memname)
from dictionary.members
where libname='X' ;
quit;
Notes (2)
53
54 libname X XLSX '/folders/myfolders/x.xlsx' ;
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: /folders/myfolders/x.xlsx
55 proc sql;
56 select nliteral(memname)
57 from dictionary.members
58 where libname='X' ;
59 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.03 seconds
60
61
62
63
64 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
74
SHEET1 |
SHEET2 |
SHEET3 |
And remove $ from your table name ,that could work.
60 data data1;
61 set x.'Sheet1'n;
62 run;
NOTE: The import data set has 1 observations and 2 variables.
NOTE: There were 1 observations read from the data set X.Sheet1.
NOTE: The data set WORK.DATA1 has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
Xia Keshan
Message was edited by: xia keshan
Message was edited by: xia keshan
What are the actual sheet names in the XLSX file?
Is it possible that once SAS removes crazy characters that Excel accepts in sheetnames that two or more of them look like the same name?
What named ranges does the XLSX file contain?
Is it possible that one or more of them look like the names for the sheets in the XLSX file?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.