Help using Base SAS procedures

Importing and exporting Excel files while retaining formulas?

Reply
New Contributor
Posts: 2

Importing and exporting Excel files while retaining formulas?

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!

Super User
Posts: 17,749

Re: Importing and exporting Excel files while retaining formulas?

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.

PROC Star
Posts: 1,555

Re: Importing and exporting Excel files while retaining formulas?

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

google these 2 words for lots of help.

Respected Advisor
Posts: 4,641

Re: Importing and exporting Excel files while retaining formulas?

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

PG
Ask a Question
Discussion stats
  • 3 replies
  • 258 views
  • 0 likes
  • 4 in conversation