Is it possible to export additional data to an excel file without overriding that current file?

[stodo53]

Hi all,

I am working with a dataset that will be updated every two weeks. I currently have code that will export the data to an excel file... however, I need this excel file to be updated every two weeks with the additional data that will be outputted from the SAS dataset file (always same variables). I've attached the skeleton of the code. The issue is that I don't want to be updating the file name to a new name every 2 weeks as we have automated the processing of this report for every 2 weeks. And, I also do not want to have multiple excel files as it will be the same variables just additional data (although, it would be fine to have multiple excel files saved for each run - but I want to have one master file as well).

/*Make Excel read dates correctly*/
%macro ExcelDateAdj(VN);
 &VN + 21916
%mend;

ods excel file="T:....\Call-Log-Patient.xlsx" style=excel;
ods excel options(frozen_headers='1' flow='tables');

ods excel options(sheet_name='Call Log');
data _null_;
 set lib path.rand_select end=done;
 if _n_ eq 1 then do;
 declare odsout t();
    t.table_start();
        t.row_start(type: 'H');
            t.format_cell(text: 'Serial No.', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'First Name', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Last Name', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'DOB', style_attr: 'width=0.6in', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Area Code', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Phone', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Diagnosis Date', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'County', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Expiry Date', style_attr: 'width=0.6in', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Phone # Status (Y/N)', overrides: 'fontweight=bold just=center vjust=center');
            t.format_cell(text: 'Interview Attempt 1 Date', overrides: 'fontweight=bold just=center vjust=center');
            t.format_cell(text: 'Interview Attempt 2 Date', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Interview Attempt 3 Date', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Interview Status (Completed/Declined)', overrides: 'fontweight=bold just=center vjust=center');
			t.format_cell(text: 'Additional Notes', style_attr: 'width=3in', overrides: 'fontweight=bold just=center vjust=center');
        t.row_end();
    end;
        t.row_start();
            t.format_cell(data: serial, style_attr: 'tagattr="format:0"');
			t.format_cell(data: firstname);
			t.format_cell(data: lastname);
			t.format_cell(data: %ExcelDateAdj(dob), style_attr:'tagattr="format:mm/dd/yyyy"');
			t.format_cell(data: area_cd);
			t.format_cell(data: phone);
			t.format_cell(data: %ExcelDateAdj(diag), style_attr: 'tagattr="format:mm/dd/yyyy"');
			t.format_cell(data: county);
			t.format_cell(data: %ExcelDateAdj(expiry_date), style_attr: 'tagattr="format:mm/dd/yyyy"');
        t.row_end();
    if done then t.table_end();
run;
ods _all_ close;

Hope this is clear and looking forward to some help on this topic!

Sara 

[ballardw]

When you use

ods excel file="T:....\Call-Log-Patient.xlsx"

That tells SAS that you intend to write a file and will replace the existing one of that name.

You might be able to have that Excel file treated as a SAS LIBRARY  and append to data treated as such (each sheet tries to be a data set). If that works Proc Append syntax should be much simpler.

[Reeza]

AFAIK you cannot with ODS EXCEL. Possibly with PROC EXPORT. You may be able to leverage Excel's auto expanding table, where any rows added automatically extend the table.

[SASKiwi]

IMHO, it would be a lot easier to manage the data updating in SAS datasets using SAS's merging, appending and updating capabilities. Once you've constructed the latest complete version of your data you can then export it to Excel, either using ODS EXCEL or PROC EXPORT. ODS EXCEL is best if you want custom formatting as your code already demonstrates. 

[stodo53]

Thanks for your reply. My main question with managing the updating in SAS datasets is the following: this program is run every 2 weeks (automatically), I was hoping it would be possible the update the "master dataset" automatically when the program is run... rather than having to update the code every two weeks to append/merge (etc) the new dataset to the master dataset. Otherwise, it defeats the purpose of having spent time to figure out how to automate the run every two weeks. 

[Kurt_Bremser]

What parts of your code do you need to change every two weeks?

[stodo53]

The code itself does not need to be changed. The same code is run every 2 weeks to produce a new random sample of individuals from an existing large SAS dataset. What I meant by "update the code" was in terms of updating the file names that needed to be appended (every 2 weeks) in order to add the new dataset to the existing "master" dataset. The same variables are being used, it's just additional observations every two weeks.

[Kurt_Bremser]

So you then create the Excel from the updated master dataset (which, of course, is kept in SAS).

[SASKiwi]

This is all absolutely possible using SAS datasets, but you still haven't explained what "file names" need to change every two weeks.

[Reeza]

Are you keeping your master data set in SAS or Excel?

I would highly recommend keeping it in SAS and then just recreating the reference Excel file each week. You can add some checks to your SAS file to make it easier to manage.

[Kurt_Bremser]

Do NOT keep data in something as unreliable and crappy as Excel. Keep your data where it's safe and easy to work with, which means you keep it in SAS. Then, every two weeks, recreate the whole Excel file from your SAS data in a simple automated process<snark>, for those poor souls who do not have the mental skills to use a proper BI tool</snark>.

[Patrick]

You can't append (update) an Excel sheet but you can re-write it. 

data days;
  day='one';
run;

/* day 1 */
libname myxlsx xlsx "c:\temp\myWorkBook.xlsx";
data myxlsx.class;
  set sashelp.class;
run;

data myxlsx.days;
  set work.days;
run;

libname myxlsx clear;

/* day 2 to n */
data days;
  day='two';
run;
libname myxlsx xlsx "c:\temp\myWorkBook.xlsx";
data myxlsx.class;
  set sashelp.class;
run;

data myxlsx.days;
  set myxlsx.days work.days;
run;

proc print data=myxlsx.days;
run;
libname myxlsx clear;

In a real life scenario I'd likely would maintain/update a SAS table and then just fully rewrite the Excel sheet. 

[Ksharp]

/*Firstly, Make an excel to test*/
proc export data=sashelp.class outfile='c:\temp\temp.xlsx' dbms=xlsx replace;
sheet='Sheet1';
run;




/*Secondly try SQL to updated the existed EXCEL file*/
libname x excel 'c:\temp\temp.xlsx' SCAN_TEXT=NO;
proc sql;
/*update data*/
update  x.'Sheet1$'n 
 set age=age+10
  where age>15 ;

update  x.'Sheet1$'n 
 set name='xxxxx'
  where age>15 ;

/*append data*/
insert into  x.'Sheet1$'n 
 select * from sashelp.class;
quit;
libname x clear;