Improper order for GUESSINGROWS=MAX ?

[Phil_NZ]

Hi all, 

Today, I put the GUESSINGROW=MAX to my code and it announce an error even I checked with some documents

My code is:

1         + proc import datafile= "C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\leniency & 
operating\dataxlsx\ARGENTINAARS"       out= ARGENTINA_sheet1      dbms= xlsx       replace;
NOTE: The previous statement has been deleted.
NOTE: Line generated by the CALL EXECUTE routine.
1         +
      guessingrows=max;      range= "Sheet1$A:X";      getnames= yes;

____________                                                                                                                        

180
ERROR 180-322: Statement is not valid or it is used out of proper order.

The problem here is the code GUESSINGROW=MAX

I check with this document and I think I put the statement above in the correct order already, I am wondering if I did anything wrong.

Thank you and warm regards.

[SASKiwi]

GUESSINGROWS is not a valid option for Excel spreadsheets. If you want to use it convert your spreadsheet to a CSV where it is a valid option.

[Phil_NZ]

So, can I ask, how many rows proc import with excel try to guess for the attributes of one variables? Is it 20 rows as default?

Thank you.

[Patrick]

With XLSX SAS inspects all rows so result as if you would (could) set guessingrow=max.

I've done in the past a test with a .xlsx where I've had a column with all numeric values and only on the millionth row a character value. The import resulted in a character variable.

[Phil_NZ]

Hi @Patrick 

Interesting, do you have any document about this statement? I am curious about that. The last time I saw a sentence "SAS will try its best to guess the attribute of a variables" but not say something explicitly as yours.

Best regards.

With XLSX SAS inspects all rows so result as if you would (could) set guessingrow=max

[Patrick · Accepted solution]

No, I don't have something in writing at hand. I've just done a test where I've created and Excel with 1M rows where I've had only in the cell in row 1M a character (and in all the cells before digits only). Then I've run Proc Import and the resulting variable was of type character. Without this character in the millionth cell the result was a numerical variable.

[SASKiwi]

My understanding is that there is a default if you use the SAS EXCEL engine and it is set in the Windows Registry where TypeGuessRows to 8 rows:

[Phil_NZ]

Hi @SASKiwi
Thank you for your reply!
So, you means GUESSINGROW =8 in your case?. Can we adjust this value?. And if it the case, why @Patrick has his result as mentioned.

Warmest regards.
Phil.

[SASKiwi]

@Phil_NZ  - I'm referring to the SAS EXCEL engine and @Patrick is referring to the SAS XLSX engine. The SAS EXCEL engine uses the Microsoft JET engine which is governed by the Windows Registry setting. Yes you can modify it - a value of 0 will maximise the number of guessing rows. However if you corrupt the Windows Registry then your PC may refuse to run so change at your own risk!

This excellent paper explains more details: https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/020-31.pdf

The DBSASTYPE option mentioned in the paper may be worth exploring.

[Patrick]

@Phil_NZ The code in your initial post shows that you're using the XLSX engine where changing the value in the registry doesn't apply. 

This change in the registry would apply for the default used with the EXCEL engine if you don't explicitly define guessingrow=

[A_Kh]

I thing the error here is in PROC IMPORT statement. DATAFILE option doesn't refer to xlsx file. 

Try this:

PROC IMPORT DATAFILE= "C:\USERS\....\ARGENTINAARS.xlsx"

proc import datafile= "C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\leniency & 
operating\dataxlsx\ARGENTINAARS"       out= ARGENTINA_sheet1      dbms= xlsx       replace;