Excel 2016 SAS Add-In: Replacing SAS Dataset without Losing Formula Reference to Dataset

[sasforumuser]

Hello,

I have SAS Add-In 8 installed for Excel 2016. On an Excel 2016 workbook, I have several worksheets which source SAS Datasets, see a picture below for a cropped example of one particular dataset.

Elsewhere in the workbook, I have cells with formulas that refer to the SAS dataset via a table reference. For example, the formula reads:

=SUMIFS(RUS20_SOLAR[LFS20_SolarOutput_kW],RUS20_SOLAR[Cooperative],Input!$B$1,RUS20_SOLAR[year],'Exhibit 15'!$A10,RUS20_SOLAR[month],'Exhibit 15'!B$7)

Where RUS20_SOLAR is the name of the table in Excel and is the SAS dataset pictured. 

I seek to replace the SAS datasets with new SAS datasets (as an example, replace RUS20_SOLAR with a new dataset but having the same column structure) while maintaining the same formula reference. When I attempt to do this manually I try two options:

First, I attempt to replace the RUS20_Solar table with my intended dataset by placing it in the location of the RUS20_Solar table (see picture).

However, after clicking OK, I am given an error, "The specified output location already contains content. Please select a different location."

The second option is I go to Manage Content and delete the table.

As soon as it is deleted, the formula becomes: 

=SUMIFS(#REF!,#REF!,Input!$B$1,#REF!,'Exhibit 15'!$A10,#REF!,'Exhibit 15'!B$7)

indicating the references to the RUS20_Solar table have been lost. 

Is there a workaround to this?

Let me know if I can clarify. I appreciate any feedback and assistance.

Thank you.

[TimBeese]

This is a tricky situation based on the way that the add-in handles data.  The add-in doesn't want to insert data over another dataset since it can't have different tables pointing to the same cells.  If you refreshed one of them, they would be writing over each other and the worksheet would turn into a mess.  Likewise, if you try to clear out the previous table and insert the new one, Excel has lost its reference since it is specifically pointing to a location within the table.

Here are 2 suggestions:

1. We have an option for whether or not the data is opened into an Excel table or not:

If you turn this option off, then when your data is inserted into the worksheet it will simply be in cells.  Now, you would lose the Excel functionality of the table object.  But, if you then add formulas that point to cell ranges, those are no longer linked to a table.  That way, if you remove the older table from the worksheet and replace it with a new one, the formulas will still be valid.

2. You could write a stored process that would essentially copy the data from the data source you want to see into a new data source that would be inserted into the document.  When you want to change the data source, you could run the stored process, and then simply refresh the data to account for the new table.  Here is a step by step description to make this more clear:

• Write a stored process that simply does something like: 

  data SASDATA.FOREXCEL;
  	set &dataset;
  run;

• This could be a prompted stored process, where the user will be prompted for the &dataset macro when it runs.  You could run this once so that your FOREXCEL data source is populated with the first table you want to use.
• The time comes where you want to replace the data in FOREXCEL with another table.  Simply run the stored process and it will prompt you for the data source.
•  
• 

  Provide the new data source, and it will overwrite your FORDATA table with the new one.  Now you can simply refresh the data table that you inserted into Excel, and it will update in place with the new columns/values.  You can even continue to use a table for it since you're never removing the table from the worksheet.

Hopefully one of these suggestions will work for you, or lead you to another possible solution.  Feel free to reach back out if you have questions on implementing these.

Tim Beese