07-22-2015 05:11 PM
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!
07-22-2015 05:26 PM
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.
07-23-2015 10:50 PM
If i recall, libname excel allows you to update sheets/ranges without changing the other cells.
google these 2 words for lots of help.
07-23-2015 11:39 PM
Using LIBNAME Excel you CAN export new values to a specific range without affecting the cells outside that range.
The rules are fairly strict:
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";
create table T as
select A+1 as A, cats(B,"_") as B
drop table xl.myData;
create table xl.myData as
select * from T;
libname xl clear;
New values are written to range myData and formulas are adjusted accordingly.