Proc import 'xlsx' doesn't work in 'guessingrows' ?

[ybz12003]

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;

[Reeza]

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. 

[ybz12003]

So is there a way to use XLXS cause I have multiple sheets.

[Reeza]

DBSASTYPE. 

[Patrick]

@ybz12003

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.