SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Update a given excel sheet with new data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Update a given excel sheet with new data

Hi,

 

I have a worksheet that I need to update monthly in excel. The excel sheet is linked to a word document; thus updating the excel sheet also updates the table in my word document.

 

I would like to use SAS to update the data in excel each month. The headings always remain the same.

 

I am using SAS version 9.4. Is there a way of replacing the data on a given sheet? To clarify, I do NOT want to overwrite the entire workbook or append data to a new sheet, as this will interfere with my ability to automatically update my word documents.

 

Thank you so much for your help.

 


Accepted Solutions
Solution
‎11-28-2016 05:17 PM
Respected Advisor
Posts: 4,606

Re: Update a given excel sheet with new data

This works for me:

 

/* Write new data to Excel workbook containing a 3-column named range called MyData */

/* Create some data (3 columns) */ 
data myDataUpdate;
do i = 1 to 10;
    x = i;
    y = i * i;
    z = i * i * i;
    output;
    end;
drop i;
run;

/* Connect to the workbook */
libname xl excel "&sasforum\datasets\myDataUpdate.xlsx";

proc sql;
/* Drop the contents of the named range */
drop table xl.MyData;
/* Write new content to the named range. 
 The number of rows in the named range will be adjusted  */
create table xl.MyData as
select * from myDataUpdate;
quit;

libname xl clear;
PG

View solution in original post


All Replies
Grand Advisor
Posts: 16,411

Re: Update a given excel sheet with new data

Do you have the Add In for Microsoft Office?

Grand Advisor
Posts: 16,411

Re: Update a given excel sheet with new data

Otherwise your best bet is to set up a named range in your excel sheet. Make sure it's large enough to hold all of your data. 

 

Then you can assign a libname to the workbook and drop and replace the information in place.

 

untested:

 

libname out xlsx 'path to xlsx file';

 

proc sql;

drop table named_range;

quit;

 

data out.named_range;

set new_data

run;

 

libname out;

Solution
‎11-28-2016 05:17 PM
Respected Advisor
Posts: 4,606

Re: Update a given excel sheet with new data

This works for me:

 

/* Write new data to Excel workbook containing a 3-column named range called MyData */

/* Create some data (3 columns) */ 
data myDataUpdate;
do i = 1 to 10;
    x = i;
    y = i * i;
    z = i * i * i;
    output;
    end;
drop i;
run;

/* Connect to the workbook */
libname xl excel "&sasforum\datasets\myDataUpdate.xlsx";

proc sql;
/* Drop the contents of the named range */
drop table xl.MyData;
/* Write new content to the named range. 
 The number of rows in the named range will be adjusted  */
create table xl.MyData as
select * from myDataUpdate;
quit;

libname xl clear;
PG
Occasional Contributor
Posts: 9

Re: Update a given excel sheet with new data

Brilliant, thanks!

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Update a given excel sheet with new data

Well, several points here.  First why do you need the Excel file if your using Word?  Secondly Office has VBA behind the scenes, dump the SAS data to CSV, then use VBA to import and process the data into either Word or Excel.  Finally, why bother using office at all, if you need to create reports then do it straight from SAS (HTML maybe?) or use a proper reporting environment.  

Post a Question
Discussion Stats
  • 5 replies
  • 288 views
  • 1 like
  • 4 in conversation