topic Re: In Excel using SAS dataset, how can I update the SAS dataset location? in Microsoft Integration with SAS

In Excel using SAS dataset, how can I update the SAS dataset location?

popples123 — Mon, 01 Aug 2016 03:29:12 GMT

Hi,

I havent been able to find an answer but also not sure exactly what to search for.

We are using SAS addin 7.1 for excel.

I have a dataset I am using but I want to change the library of where this dataset is, is there a way within excel to replace the current dataset with the new dataset (which is the same format just different library location)?

I would prefer to not have to bring in a new dataset table as I will need to remap a huge number of formulas that reference that dataset.

Thanks

Re: In Excel using SAS dataset, how can I update the SAS dataset location?

ballardw — Mon, 01 Aug 2016 20:08:11 GMT

I am not really sure I understand your problem but it may be that you only need to change the Library definition to point to a different location.

Suppose you have the data set ThisJob in library Here that the Excel is referencing and that library Here is pointing to "C:\thisfolder\folderA";

To use a different dataset ThisJob located in "C:\thisfolder\FolderB" then use something like:

Library Here "C:\thisfolder\FolderB";

later references to Here.ThisJob would use the one the FolderB.

However, for this to work you might need to close down Excel, or at least the connection to that dataset, and restart to accept the new dataset definition when it connects to SAS.

Re: In Excel using SAS dataset, how can I update the SAS dataset location?

Patrick — Sat, 01 Oct 2016 03:57:49 GMT

In case you can't just change the library definition (=same libref but another path) but you actually need to change the name of the libref in your specific document:

I haven't found a menu item in AMO which allows you to do this BUT you can do it using the migration wizard (worked for me).

https://support.sas.com/documentation/cdl/en/bidaag/69541/HTML/default/viewer.htm#n0oy93cwp7oc51n14p509zbjjzdz.htm

On my laptop this wizard can be found under:

C:\Program Files (x86)\SASHome\x86\SASAddinforMicrosoftOffice\7.1\MigrationWizard.exe and changing the library was a simple point & click operation.

Another hack option which also worked for me with Excel 2013 (you want to try this on a copy of your Excel):

You can open a .xlsx with an archiving tool like WinZip or 7-Zip and then modify the path directly in the XML. There is a risk that you corrupt the .xlsx - but it worked for my with my sample file and changing the libref from "sashelp" to "sasuser"