SAS Enterprise Guide uses a third-party product called SpreadsheetGear to import Excel files when using the GUI Import Data task. SpreadsheetGear cannot handle certain built-in Excel functions such as XLOOKUP(). When SAS Enterprise Guide imports an Excel file that contains certain built-in functions, the resulting SAS data set contains missing values.

The workaround is to force the Import Data task to use the Microsoft Access Connectivity Engine (ACE) instead. To do this, specify the following option in the <appSettings /> section within the SEGuide.exe.config file (which is located in the Enterprise Guide installation folder):

It is recommended that this option be used only when necessary, because the use of SpreadsheetGear provides with some extra functionality that the ACE driver does not.

The use of this option requires that either Microsoft Office/MS 365 be installed or that the stand-alone ACE driver be installed. Also, the bitness (x86 or x64) of the installed ACE driver must match the bitness of the installed version of SAS Enterprise Guide.