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!
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.
If i recall, libname excel allows you to update sheets/ranges without changing the other cells.
google these 2 words for lots of help.
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";
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.