topic Importing and exporting Excel files while retaining formulas? in SAS Procedures

Importing and exporting Excel files while retaining formulas?

PhdWho — Wed, 22 Jul 2015 21:11:28 GMT

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!

Re: Importing and exporting Excel files while retaining formulas?

Reeza — Wed, 22 Jul 2015 21:26:13 GMT

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.

Re: Importing and exporting Excel files while retaining formulas?

ChrisNZ — Fri, 24 Jul 2015 02:50:29 GMT

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

google these 2 words for lots of help.

Re: Importing and exporting Excel files while retaining formulas?

PGStats — Fri, 24 Jul 2015 03:39:54 GMT

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.