I read that "A cell can contain no more than 32,767 characters. " But how to read them? I have long texts in cells. But when I read it using proc import, it cuts off in 255 characters. Some of the cells contain about 500 characters. Thank you for your help.
PROC IMPORT OUT= WORK.managers
DATAFILE= "e:\Users\mhollifi\Desktop\MaxWell read excel\pull
08to08.xlsx"
DBMS=EXCEL REPLACE;
RANGE="Request3$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Alphabetic List of Variables and Attributes# Variable Type Len Format Informat Label845
All_Managers | Char | 255 | $255. | $255. | All Managers |
All_Managers_Role_Description | Char | 255 | $255. | $255. | All Managers_Role Description |
If you want the first sheet then just don't specify anything.
Also try just use the XLSX libname engine.
libname in xlsx "e:\Users\mhollifi\Desktop\MaxWell read excel\pull08to08.xlsx";
proc copy inlib=in outlib=work;
run;
Data type and length on variables using proc import are determined by guessing the rows. Use GUESSINGROWS=n higher number ( your expected count of records). By default it is 20, so it those 20 records it found that length and applied to all the records.
GUESSINGROWS=n can be applied only to delimited files.
As @Reeza mentioned change it to XLSX and try or convert your file to CSV and use GUESSINGROWS=n
Try a different DBMS, I believe the limitation is actually an Excel limitation in the transfer mechanism but it's been a while since I looked into this.
Try DBMS=XLSX
And worse case, can you convert the file to CSV and then import it?
it doesn't work. When I click "import files" on the left it gives me only 'dbms =excel replace.' But when I change this to xlsx I got totally error message...
1 PROC IMPORT OUT= WORK.managerx
2 DATAFILE= "e:\Users\mhollifi\Desktop\MaxWell read excel\pull
3 08to08.xlsx"
4 DBMS=xlsx;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
5 RANGE="'Request 3$'";
NOTE: The previous statement has been deleted.
6 GETNAMES=YES;
NOTE: The previous statement has been deleted.
7 MIXED=NO;
-----
180
8 SCANTEXT=YES;
--------
180
9 USEDATE=YES;
-------
180
10 SCANTIME=YES;
--------
180
11 TEXTSIZE=2024;
--------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
12 RUN;
Some options doesn't support when DBMS=XLSX, try deleting SCANTEXT,..etc
Thank you for your reply. But it still not working. I tried to convert to csv file, which destroyed the data. It just cut off the multi-lines in one cell. So I need to keep xlsx. Thank you for your help
20 PROC IMPORT OUT= WORK.managerx
21 DATAFILE= "e:\Users\mhollifi\Desktop\MaxWell read excel\pull
22 08to08.xlsx"
23 DBMS=xlsx;
24 RANGE="'Sheet1$'";
25 GETNAMES=YES;
26 run;
Couldn't find range in spreadsheet
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
If you want the first sheet then just don't specify anything.
Also try just use the XLSX libname engine.
libname in xlsx "e:\Users\mhollifi\Desktop\MaxWell read excel\pull08to08.xlsx";
proc copy inlib=in outlib=work;
run;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.