BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

View solution in original post

25 REPLIES 25
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26

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.

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

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
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
PGStats
Opal | Level 21

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

PG
OS2Rules
Obsidian | Level 7

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
Calcite | Level 5

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
Barite | Level 11

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 --<-----
PaigeMiller
Diamond | Level 26

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)

--
Paige Miller
jakarman
Barite | Level 11

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 --<-----
PhrzbyPhil2406
Calcite | Level 5

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

PGStats
Opal | Level 21

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
art297
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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