topic Re: Data from Excel using table formulas are blank. in Microsoft Integration with SAS

Data from Excel using table formulas are blank.

GenDemo — Mon, 06 Aug 2018 23:53:04 GMT

Hi All

I am having trouble when importing data from Excel to EG. The sheet in Excel uses table formulas, for example "=+VLOOKUP([@Campaign],map!A:B,2,0)" . These cells turn out blank in SAS. I have discovered that if you just replace the table formula with cell reference formulas, for example "=+VLOOKUP(D1882,map!A:B,2,0)", then it works fine.

Why would this happen?

Is there a way to fix it, apart from changing all the formulas in the workbook.

I've never encountered this issue with base SAS on the desktop edition, as and when the proc import failed then I would just hard code the import with a data step and 'infile' statement. But our company moved to a server setup and we can't use proc import, nor the 'infile' statement. All import have to be done through the import data wizard - hence I am struggle to find ways around this.

I'd appreciate your import.

Thnx, GD

Re: Data from Excel using table formulas are blank.

Tom — Tue, 07 Aug 2018 00:34:36 GMT

Are you using the EG gui tool for importing the Excel from your PC? Or are you running PROC IMPORT step on the server pointing to a copy of the original Excel file? Or using XLSX libname engine?

Re: Data from Excel using table formulas are blank.

GenDemo — Tue, 07 Aug 2018 02:14:17 GMT

Im using the import wizard/GUI in EG.

Re: Data from Excel using table formulas are blank.

Tom — Tue, 07 Aug 2018 04:37:06 GMT

You should submit the problem to SAS support.

Re: Data from Excel using table formulas are blank.

GenDemo — Wed, 08 Aug 2018 22:50:33 GMT

Hahahaha, ok. Thnx 🙂