Print data to excel

[noobs]

I am using this code in Program node in SAS Enterprise Guide to export output dataset into Microsoft Excel worksheet.

ods tagsets.excelxp file=_webout 
options(embedded_titles="yes"
        print_header='AC_SP_C2_C5_C6_T2_Classes'
                            sheet_name='C2 C5 C6 and T2 Classes');
proc print data=work.Filter_C2_C5_C6_T2 noobs;
run;
ods tagsets.excelxp close;

Is it possible to control range of cells where this data gets pasted in worksheet C2 C5 C6 and T2 Classes so that every time the project runs and new data is generated, it gets appended at bottom.

I looked into options for PROC EXPORT but it does not support RANGE option; like PROC IMPORT that allows you to import data from specific range of cells using option similar to RANGE="C2 C5 C6 and T2 Classes$B4:D10";

I am open to other solutions that may allow me to achieve this task of exporting dataset at end of existing data in worksheet and not totally replacing it.

Thanks,

Dhanashree

[art297]

: I don't want to get your hopes up as my suggestion may not work in EG and/or whatever system you are working on.

Two of my list colleagues, and I, are presenting a paper at this year's SGF that comes awfully close to what you are asking for.  Chris or someone more familiar with EG would have to chime in to let us know if it could be modified to work in EG.

I'm pretty sure that the full method wouldn't work in EG.  It was designed to work in base SAS where one can right click on a SAS dataset in the SAS Explorer window and select an action.  The action, in this case, is to run the macro that the paper presents.

The macro creates a function, with Proc FCMP, that writes the dataset (either with or without a variable name header row) to one's system's clipboard,

Then the macro writes and runs VBS script that creates the workbook and pastes the clipboard contents starting either starting in cell A1 or in a cell specified by the user, and then saves and closes the workbook.

You would definitely have to modify the macro as it uses one base SAS feature that I'm pretty sure wouldn't run in EG, namely the use of the window statement to let users identify the upper left hand corner where they want the table written.

As I read your request, you would want to change the VBS script so that it simply opened a workbook rather than always create a new one and, instead of specifying the cell to begin, simply go to the last used row in a workbook and paste the clipboard starting at a specified column.

If you think that approach might be in the right direction, let me know and I'd be glad to send you an advance copy of the macro.

[SASKiwi · Accepted solution]

The EXCELXP tagset creates an XML version of a spreadsheet and you have to create the entire spreadsheet from scratch with this technique - appending to an existing spreadsheet is not supported.

If you kept all of the data you require to export in a permanent SAS dataset, appending the latest data as required, then exporting all of the data each time that might be a workable solution for EXCELXP. 

[noobs]

Thanks SASKiwi, I like your suggestion about reading input data from excel spreadsheet (same as permanent dataset), doing computations and saving it back to that sheet. It might resolve what I am trying to do.

Thanks a bunch!