Hello:
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
datafile='\\abc\monthly_data\Test\Report2017.xlsx'
dbms=xlsx replace;
sheet=variables;
getnames=yes;
datarow=2;
guessingrows=159503;
run;
quit;
75 proc import out=NC
76 datafile='\\abc\monthly_data\Test\Report2017.xlsx'
77 dbms=xlsx replace;
NOTE: The previous statement has been deleted.
78 sheet=variables;
79 getnames=yes;
80 datarow=2;
81 guessingrows=159503;
------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
82 run;
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
83 quit;
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.
So is there a way to use XLXS cause I have multiple sheets.
DBSASTYPE.
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.
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.