DATA Step, Macro, Functions and more

Change TypeGuessRows in Program?

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,909
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,909

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,942

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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,909

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,942

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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,919

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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,909

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,919

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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?

Posted in reply to PaigeMiller

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?

Posted in reply to PaigeMiller

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

Trusted Advisor
Posts: 3,212

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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,909

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)

Trusted Advisor
Posts: 3,212

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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?

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 4,919

Re: Change TypeGuessRows in Program?

Posted in reply to PhrzbyPhil2406

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,467

Re: Change TypeGuessRows in Program?

Posted in reply to PaigeMiller

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 and locked.

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

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