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.
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.