Dynamic Import of Excel Columns in SAS Enterprise Guide

[cepp0]

Hi everyone,

I am trying to implement a dynamic import of columns from an Excel file using SAS Enterprise Guide. Currently, I use the "Import Data" node to import the data, but I would like to know if there is a way to make the column import flexible so that new columns added to the Excel file are imported automatically without having to manually adjust the node configuration.

I have heard about dynamic named ranges in Excel that automatically update when new columns are added. Has anyone had experience using these named ranges in SAS Enterprise Guide? If so, could you share an example of the configuration or script that allows importing a dynamic named range?

Any suggestions or practical examples would be greatly appreciated!

Thank you very much

[Tom]

Are you using a Enterprise Guide tool to import the XLSX file? Which tool?

Or are you using SAS code to import the XLSX file?  If so what code are you using? PROC IMPORT?  Libname using the XLSX engine?

[cepp0]

Thank you for your responses. To clarify, I am currently using the native "Import Data" tool in SAS Enterprise Guide to import the XLSX file. I am not using SAS code directly at this moment.

My goal is to make the column import dynamic so that any new columns added to the Excel file are automatically included in the import without needing to manually adjust the configuration each time.

Is there a way to achieve this using the "Import Data" tool in SAS Enterprise Guide? If not, what would be the best approach to accomplish this, possibly using SAS code like PROC IMPORT or the libname statement with the XLSX engine?

[ChrisHemedinger · Accepted solution]

For "dynamic" updates like this, where the structure of the file may change, I suggest using PROC IMPORT or LIBNAME XLSX. If you have local XLSX files that you are reading with the Import Data task, you might first need to copy them to the SAS server file system so that you can use these programming methods.

Use the Copy Files task to copy the XLSX file(s) to a folder in your SAS session, then connect a Program node that uses LIBNAME XLSX (I'd start with that) to read the Excel sheets into a SAS data set.

[cepp0]

I'm also using a sas promt, to do input for table ranges so if it changes it could be easier and flexible, then i'll put values creanting range variable in proc imports. It's working! Thank's for your tips!