BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
azorachase
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
Reeza
Super User

Do you have the Add In for Microsoft Office?

Reeza
Super User

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;

PGStats
Opal | Level 21

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
azorachase
Calcite | Level 5

Brilliant, thanks!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 5 replies
  • 9920 views
  • 1 like
  • 4 in conversation