DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 297

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

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;

 

Super User
Posts: 17,829

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

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. 

Super Contributor
Posts: 297

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

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

Super User
Posts: 17,829

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

DBSASTYPE. 

Respected Advisor
Posts: 3,892

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

[ Edited ]

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

 

Ask a Question
Discussion stats
  • 4 replies
  • 130 views
  • 3 likes
  • 3 in conversation