topic Re: Proc import 'xlsx' doesn't work in 'guessingrows' ? in SAS Programming

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

ybz12003 — Tue, 06 Jun 2017 13:10:49 GMT

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;

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

Reeza — Tue, 06 Jun 2017 13:25:11 GMT

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.

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

ybz12003 — Tue, 06 Jun 2017 13:26:45 GMT

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

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

Reeza — Tue, 06 Jun 2017 13:27:46 GMT

DBSASTYPE.

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

Patrick — Tue, 06 Jun 2017 13:40:47 GMT

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