DATA Step, Macro, Functions and more

Change TypeGuessRows in Program?

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,618
Accepted Solution

Change TypeGuessRows in Program?

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?


Accepted Solutions
Solution
‎10-31-2014 02:12 PM
Trusted Advisor
Posts: 1,618

Re: Change TypeGuessRows in Program?

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 Smiley Happy

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: Change TypeGuessRows in Program?

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.

Trusted Advisor
Posts: 1,618

Re: Change TypeGuessRows in Program?

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.

Super User
Super User
Posts: 7,407

Re: Change TypeGuessRows in Program?

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.

Respected Advisor
Posts: 4,651

Re: Change TypeGuessRows in Program?

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

PG
Trusted Advisor
Posts: 1,618

Re: Change TypeGuessRows in Program?

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?

Respected Advisor
Posts: 4,651

Re: Change TypeGuessRows in Program?

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

PG
Super Contributor
Posts: 358

Re: Change TypeGuessRows in Program?

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.

Occasional Contributor
Posts: 6

Re: Change TypeGuessRows in Program?

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

Valued Guide
Posts: 3,208

Re: Change TypeGuessRows in Program?

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.

---->-- ja karman --<-----
Trusted Advisor
Posts: 1,618

Re: Change TypeGuessRows in Program?

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)

Valued Guide
Posts: 3,208

Re: Change TypeGuessRows in Program?

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.

---->-- ja karman --<-----
Occasional Contributor
Posts: 6

Re: Change TypeGuessRows in Program?

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

Respected Advisor
Posts: 4,651

Re: Change TypeGuessRows in Program?

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

PG
PROC Star
Posts: 7,363

Re: Change TypeGuessRows in Program?

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 25 replies
  • 1393 views
  • 0 likes
  • 8 in conversation