Data from Excel using table formulas are blank.

[GenDemo]

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

[Tom]

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?

[GenDemo]

Im using the import wizard/GUI in EG. 

[Tom]

You should submit the problem to SAS support.

[GenDemo]

Hahahaha, ok. Thnx 🙂