Change TypeGuessRows in Program?

[PaigeMiller]

I am using PROC IMPORT to read Excel spreadsheets. The SAS documentation says you can go into the Windows Registry and change the TypeGuessRows key to change the behavior of PROC IMPORT. I have done that and it works.

But what if I have one program where I need TypeGuessRows equal to zero, and another program where I need TypeGuessRows equal to 8? It would be great if this could be changed in the SAS code programatically, instead of performing the task manually in the Windows registry. How can this be done?

[RW9]

Don't see how you would do that as this is not actually a SAS registry key, but and Office one.

If I might be so bold, I would suggest to not use Excel/Proc import at all.  For data save to CSV, then datastep import.  This gives you/SAS/your program as much control as possible to do what you like.  Generally I have a bit of a paddy if data arrives in Excel format.

[PaigeMiller]

Your suggestion is fine for data sets I create ... but I work in a company where we have reporting systems that create Excel spreadsheets, and I have no say or input in that, yet I'm expected to use these spreadsheets.

So, I'm still hoping for a workaround.

Thanks.

[RW9]

Automate saving of Excel to CSV files either via VBA or one of the various utilities around.  Just validating that a file has loaded and looks as expected takes twice the time due to Excel being "helpful".

If its XLSX you could in theory just read the underlying XML files within the XLSX zip.

[PGStats]

Most users would not benefit much from such a feature since modifying the Windows registry requires Admin privileges. Just curious, under what circumstances is typeguessrows=8 a better choice? _ PG

[PaigeMiller]

PGStats wrote:

Most users would not benefit much from such a feature since modifying the Windows registry requires Admin privileges. Just curious, under what circumstances is typeguessrows=8 a better choice? _ PG

Honestly, I don't know the answer to that , I have a program where I made a note a loooong time ago that typeguessrows needed to be set to 8 for it to work, and I no longer remember why. Yes, upon thinking about this today, it seems that typeguessrows=0 makes sense in any situation that I can think of today.

What would be a reason to have a default of 8?

[PGStats]

I wouldn't want to second guess people living on another planet (Microsoft developers). But performance usually wins over absolute truth. - PG

[OS2Rules]

Hi:

Not sure if this will help, but we use "DBMS=excel" and "DBMS=xls" on different occasions.  One (don't remember which) will use the entire

document for GuessingRows, and the other does not.  Perhaps you can find out how this is done and create different DBMS types as

needed.

[PhrzbyPhil2406]

As a programmer/analyst, I want my programs to be readable/maintainable by whoever follows me.  In this regard, I do not want to bury a non-default value in a registry.

Rather, I want everything possible to be in program code.  This certainly includes shared code (via, say, %INCLUDE).

[jakarman]

Why use proc import?

CSV files are very well readable by a datastep. "Proc import" will do that in the end also.

IT is only adding additional complexity when you have ad well defined data-structure and going to process that with a guessing logic. I bet that guessing always can give an unpredictable not wanted result.

Going for the xslx files they could be read using the xml approach as those Excel files are zipped xml-files. Dumping the ACE-driver of Microsoft for that will give a more reliable processing. It is the SAS development that missed the MS change as of 2007. It is almost the same type (some extensions) as open-office OASIS is using.

Changing OS level settings requiring ADMIN rights is just a bad idea. It is going to be locked sooner or later os of IT governance policies. At many sites with a implemented desktop strategy it is already locked for many years.

[PaigeMiller]

I don't have CSV files. I don't have XLSX files. I have XLS files. I need a solution for XLS files.

I agree completely.

can you point to specific conversion VBA routines or utilities? These would need to be something I could call from within SAS, perhaps via the X command (or similar)

[jakarman]

PaigeMiller sorry but those types are becoming from old ages. Let us be happy you did not aks for wks files (lotus 123).

You can still use the libname statement with Excel SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

It is having mentioned the option for guessing rows as Windows parameter SAS/ACCESS(R) 9.3 Interface to PC Files: Reference. It varies on all the Office versions.

Converting it first to a csv (using VBA) would be a good circumvention for that.

[PhrzbyPhil2406]

Also, the guessing parm/logic is worthless for sparse columns.  You are asking for trouble.

[PGStats]

It's not, when typeGuessRows is = 0 (in the Windows registry) and MIXED=YES. Any column that contains a non NUMERIC value is imported as characters. There is no perfect translation from a cell-based to a column-based data organisation. There is always a compromise between ease of use and generality. - PG

[art297]

Paige: I'm late seeing this thread, but I think the option you're asking for is already there! Typeguessingrows is simply controls the maximum number of rows that can be checked. The guessingrows statement, in proc import, actually controls how many rows will be reviewed.