06-06-2017 09:10 AM
I got an error message by use 'guessingrows' in proc import 'xlsx'. I would like to make sure the length of the cell is long engouth to hold the words, how to fix it? Please help. Thanks.
proc import out=NC
75 proc import out=NC
77 dbms=xlsx replace;
NOTE: The previous statement has been deleted.
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
06-06-2017 09:25 AM
GUESSINGROWS is not a valid option for DBMS=XLSX.
Excel is not a database and doesn't enforce types.
You can try DBSASTYPE to force a type, but my suggestion would be to create a CSV file and import that.
06-06-2017 09:37 AM - edited 06-06-2017 09:40 AM
I believe the XLSX engine does the guessingrow bit implicitly and always analysis all rows.
I've just run a test with the first 1 million rows in column A having a value of "a" and row 1000001 having a value of 'bbbbb'. The length of the variable in the SAS table was 5 and no source values got truncated.