Importing and exporting Excel files while retaining formulas?

[PhdWho]

Hello SAS Community,

My first time posting so please forgive my ignorance in advance. I have encountered an issue that I don't know how to solve nor can I find any information on it (perhaps because it is not possible).

I am merging two Excel files together using Proc SQL in SAS for a customer (along with other data cleaning procedures). They have requested that the formulas they have used in their Excel files remain. Is there a way to keep the formulas when importing an Excel file into SAS, conduct my merge, and export back to Excel with the formulas?

Example: They have several calculated fields and Vlookups within their files.

Thank you in advance!

[Reeza]

Not easily, but you can only export the new data to the excel file instead.

Use either some VBA or DDE to export to specific ranges.

[ChrisNZ]

If i recall, libname excel allows you to update sheets/ranges without changing the other cells.

google these 2 words for lots of help.

[PGStats]

Using LIBNAME Excel you CAN export new values to a specific range without affecting the cells outside that range.

The rules are fairly strict:

• The range of cells must include column names in the first row.
• Before you overwrite the range, you must drop the table
• You must overwrite the range with the same number of columns and lines

For example, I have a named range called myData in workbook NewTest.xlsx with columns A and B. Outside range myData, I have formulas referring to values from the range. The following works

libname xl "&sasforum\Datasets\newTest.xlsx";

proc sql;

create table T as

select A+1 as A, cats(B,"_") as B

from xl.myData;

drop table xl.myData;

create table xl.myData as

select * from T;

quit;

libname xl clear;

New values are written to range myData and formulas are adjusted accordingly.

PG