topic Importing Excel 2010 Sas Version Problem in SAS Programming

Importing Excel 2010 Sas Version Problem

Kody_devl — Thu, 02 Mar 2017 00:10:25 GMT

I am trying to transfer a program that I wrote to another individual's machine as I am leaving the company.

One of the first steps in the program is to import an Excel file, Period.XLSX. I use this code (and it has worked and still works for almost a year)

However my co-worker can't get this code to work on his machine (we have worked for 1/2 day together to try to import this Excel file)

Here is my working CODE:

/* Import Periods Lookup Table */

proc import

datafile = "/sas/sasperm3_prod/RiskInfrastructure/Crill/QRM/Files/Import/Period.xlsx"

out= Days dbms= xlsx replace;

getnames= yes;

range= 'Days';

run;

After much testing we have determined that this code fails on two other machines that have the latest SAS version which appears to be the only difference between me and them.

My Version is 7.1 HFI(7.100.0.2002)

Their Version 7.2

Their error message says that the period.xlsx is corrupt, but it is not.

What is causing this? HELP

PS He is able to import the file using the wizard.

MORE INFORMATION ADDED: The 7.2 User can import the Period.xlsx file successfully with my code if the Excel file sits in my personal directory without corruption but if we move the Excel file to his personal directory, it fails (corrupts). I think this will give a key to the problem and answer

Re: Importing Excel 2010 Sas Version Problem

art297 — Wed, 01 Mar 2017 22:50:18 GMT

Is it possible that their machines or EG versions are 64-bit and your's is 32-bit, or visa-versa?

Art, CEO, AnalystFinder.com

Re: Importing Excel 2010 Sas Version Problem

Kody_devl — Wed, 01 Mar 2017 22:53:43 GMT

My machine is 64 bit and so is his machine.

Re: Importing Excel 2010 Sas Version Problem

art297 — Wed, 01 Mar 2017 22:58:00 GMT

Are your EG versions both 64-bit?

Re: Importing Excel 2010 Sas Version Problem

Kody_devl — Wed, 01 Mar 2017 23:01:41 GMT

I don't know about his but my SAS version says 32 bit and our admins say that it is OK to run 32 Bit SAS on a 64 bit machine. I have had no problems. I don't trust everything that they say.

Re: Importing Excel 2010 Sas Version Problem

art297 — Wed, 01 Mar 2017 23:07:58 GMT

Their being on 64-bit EG could be the problem. Another possibility: is there a chance that someone has the Excel file open when they try to import it?

Art, CEO, AnalystFinder.com

Re: Importing Excel 2010 Sas Version Problem

Kurt_Bremser — Thu, 02 Mar 2017 10:58:21 GMT

How do you move the file to his directory? Using UNIX cp?

Re: Importing Excel 2010 Sas Version Problem

Kody_devl — Thu, 02 Mar 2017 11:07:35 GMT

Great question because we had suspicions here.

We use and FTP program called WIN SCP

Re: Importing Excel 2010 Sas Version Problem

Oligolas — Thu, 02 Mar 2017 12:14:17 GMT

Hi,

the SAS version shouldn't be a problem here, but the SAS modules installed could be.

Run proc setinit;run; on both machines and check if you have SAS/ACCESS installed.

If the other machine does not have any SAS/ACCESS products licensed, then the only types of data source files available to you are .CSV, .TXT, and delimited files.

Cheers,

Re: Importing Excel 2010 Sas Version Problem

Kurt_Bremser — Thu, 02 Mar 2017 12:13:50 GMT

Then make sure that you use "binary" as transfer method. Otherwise it might use text and scramble the file.

IMO there should be no bitness problems when using dbms=xlsx, as SAS uses a built-in decoder/encoder for that (and not anything supplied by MS). And xlsx is zip-compressed xml, so basically compressed text.