topic Re: Change TypeGuessRows in Program? in SAS Programming

Change TypeGuessRows in Program?

PaigeMiller — Fri, 31 Oct 2014 13:16:27 GMT

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?

Re: Change TypeGuessRows in Program?

RW9 — Fri, 31 Oct 2014 13:26:03 GMT

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.

Re: Change TypeGuessRows in Program?

PaigeMiller — Fri, 31 Oct 2014 13:29:17 GMT

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.

Re: Change TypeGuessRows in Program?

RW9 — Fri, 31 Oct 2014 13:35:33 GMT

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.

Re: Change TypeGuessRows in Program?

OS2Rules — Fri, 31 Oct 2014 14:25:14 GMT

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.

Re: Change TypeGuessRows in Program?

PGStats — Fri, 31 Oct 2014 15:17:14 GMT

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

Re: Change TypeGuessRows in Program?

PaigeMiller — Fri, 31 Oct 2014 15:23:25 GMT

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?

Re: Change TypeGuessRows in Program?

PhrzbyPhil2406 — Fri, 31 Oct 2014 15:24:13 GMT

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

Re: Change TypeGuessRows in Program?

jakarman — Fri, 31 Oct 2014 15:29:29 GMT

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.

Re: Change TypeGuessRows in Program?

PaigeMiller — Fri, 31 Oct 2014 15:36:00 GMT

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)

Re: Change TypeGuessRows in Program?

PhrzbyPhil2406 — Fri, 31 Oct 2014 15:36:41 GMT

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

Re: Change TypeGuessRows in Program?

PGStats — Fri, 31 Oct 2014 16:29:56 GMT

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

Re: Change TypeGuessRows in Program?

jakarman — Fri, 31 Oct 2014 16:38:19 GMT

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.

Re: Change TypeGuessRows in Program?

PGStats — Fri, 31 Oct 2014 16:51:25 GMT

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

Re: Change TypeGuessRows in Program?

art297 — Fri, 31 Oct 2014 17:46:30 GMT

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.

Re: Change TypeGuessRows in Program?

PaigeMiller — Fri, 31 Oct 2014 17:49:57 GMT

As far as I can tell, guessingrows cannot be used with Excel files.

Is that correct, or not?

Re: Change TypeGuessRows in Program?

jakarman — Fri, 31 Oct 2014 17:57:40 GMT

Yep it is there for delimited file-types Base SAS(R) 9.2 Procedures Guide

With Base SAS(R) 9.3 Procedures Guide, Second Edition This one maintained in the SAS registry not to be confused with the Windows registry.

The sas registry is as the same location as the personal SAS profile dataset (catalog type). An xls file is not a delimted file isn't it.

Also delimited files have the question is the field numeric (converted to floating IEEE) or is it character. Using the limited ranges of the characters 0-9 is more like a constraint and not having the same meaning as a measurement. Those intention in delimited file is not defined, for this shortcoming that is why xml has been designed.

Re: Change TypeGuessRows in Program?

art297 — Fri, 31 Oct 2014 18:03:25 GMT

: You are correct! I wonder why they limited it just the delimited files.

Re: Change TypeGuessRows in Program?

RW9 — Fri, 31 Oct 2014 18:10:23 GMT

In regards to conversion from Excel to CSV, there a fair few solutions out there. One from a quick Google is the following:

https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

Basically a small script to open Excel and use the functionality within Excel to save as CSV.

Re: Change TypeGuessRows in Program?

PaigeMiller — Fri, 31 Oct 2014 18:12:36 GMT

RW9 wrote:

In regards to conversion from Excel to CSV, there a fair few solutions out there. One from a quick Google is the following:

https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

Basically a small script to open Excel and use the functionality within Excel to save as CSV.

Thanks, I will give this a try