topic Re: Import from Excel Regedit in SAS Programming

Import from Excel Regedit

sekarpc — Tue, 17 Sep 2019 16:45:26 GMT

I got a new computer with Windows 10 and Office 19. I have to import from Excel all the time and in my previous computer with Windows 10 and Office 16, I had set the typeguessrows to 0, in order that the import worked properly (sometimes the first few rows are empty for some variables). I thought that I figured where the typeguessrows was in the new one and set it to 0. Yet, the import is not working properly. I wanted to attach a screen shot, but I am unable to do so.

It was at

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

I set the TypeGuessRows to 0 there, yet the import does not work right.

Anyone can help me solve this problem?

Re: Import from Excel Regedit

Reeza — Tue, 17 Sep 2019 16:51:46 GMT

What does the 'import does not work right' mean?

Did you upgrade SAS as well?

Did you make sure to set VALIDVARNAMES to V7? If you're getting spaces in your name this is why.

Re: Import from Excel Regedit

sekarpc — Tue, 17 Sep 2019 17:09:40 GMT

In answer to what does the "import does not work right" mean:

For instance, a particular variable named "Date_Interview_Received" has certain texts-some of them are dates, but there are some that are not. For instance I know and I see in the original excel file that one entry says "Family declined Interview". By setting the typeguessrows to 0, the whole column (in my previous computer) would import as text and then I will deal with it all later. But I see that the import has decided that the column is all dates and it has not imported as text. That particular person has missing under date interview received rather than that text.

Do I make sense?

Re: Import from Excel Regedit

Reeza — Tue, 17 Sep 2019 17:11:44 GMT

Yes, but unfortunately with Excel files, SAS has to guess at types and it doesn't guarantee that the guesses will be the same. You did not answer the question if SAS updated as well. If you updated your SAS version, I would expect to see this behaviour.

Re: Import from Excel Regedit

sekarpc — Tue, 17 Sep 2019 17:16:44 GMT

I do not think that I updated my SAS. It was 9.4 before and it is 9.4 now. However, in the previous machine, it was SAS 9.4 TS Level 1M4 and in the new one it is SAS 9.4 TS Level 1M6.
I don't know if that makes a difference.
Padmini

Re: Import from Excel Regedit

Reeza — Tue, 17 Sep 2019 17:22:23 GMT

Yes, you've changed both your Excel and SAS versions so I don't think expecting the same behaviour in a guessing procedure is a reasonable expectation at this point. SAS is always updating the Excel engine so hopefully you're getting better data but relying on Excel formats is not a good idea in any language.

Re: Import from Excel Regedit

PGStats — Wed, 18 Sep 2019 04:03:06 GMT

Shouldn't you be changing the typeGuessRows value at

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\19.0\Access Connectivity Engine\Engines\Excel ?

Re: Import from Excel Regedit

sekarpc — Wed, 18 Sep 2019 13:09:44 GMT

Actually, I have solved the problem-SAS technical support helped me. Interestingly, it was Office\15.0\Access Connectivity Engine\engines\Excel that worked. There is no 19.0 (I looked for it before all this). There is no logic behind this. I am glad I got it to work, but I would not have guessed for the world.
Thanks to you all.

Re: Import from Excel Regedit

Reeza — Wed, 18 Sep 2019 17:05:45 GMT

Yeah, if you're on 365 it updates often and the version numbers do not match the 'displayed version' numbers so that doesn't help any.