Writing Data to Specific Cells in an Excel Spreadsheet

[LosEndos]

We have a reporting process that is being migrated from another technology onto our SAS environment. Part of this process generates summary data and exports it into a specific range of cells in a pre-existing Excel spreadsheet (actually into a copy of a master template file). The number of generated values is unlikely to change and so will always be written to the same cells, e.g. B1:B12 on sheet1 of the relevant workbook.

I believe that there are a couple of obvious ways that we could replicate this functionality programmatically in SAS but neither appear viable due to on-site technical constraints.

1. I think that the most straightforward way would be via the Excel libname engine.  However, this is not available to us due to 32/64 bit incompatibility issues with Excel on the server.

2. Although probably less straighforward (and possibly a tad outdated) we also attempted utilising DDE filename statement options.  However, this requires the X command options to be enabled. The data export jobs need to run as part of a scheduled LSF flow on a BI server (version 9.2) where command line options are, by default, switched off (and due to site security policy it is unlikely that this will be changed).  

Can anyone suggest an alternative approach?

Thanks, in advance, for any suggestions.

[Linlin]

try this:

go to your excle file, select cell b1:b12, then right click, select name a range, put a name in.

data yourlibname.your_range_name;

  set your dataset;

run;

libname yourlibname clear;

[Haikuo]

OP,

I am not sure whether it makes a difference or you have already tried for no vail, but have you tried to use 'systask command' in replace of 'x' command?

Haikuo

[Linlin]

Hi Haikuo,

I’d like to delete my stupid reply without deleting yours. Would you please reply to the OP’s post directly?

Thanks - Linlin

[Haikuo]

LinLin,

Don't delete, just empty the contents. I have already update my post implying no contents of your post.

Regards,

Haikuo

[Lida]

Hi LinLIn,

Unfortunately I cannot make your code running in a way I want. For some reason sas insert sas data not in the range I specified in excel file. I named a selected range in the excel file as table1 ($C$4:$E$7)and use this as a reference in the data step in sas. It does not work. The sas data are placed in the range $A$1:$C$4 instead of where I want it. Any ideas why is that?

Here is my sas code:

libname myxls '..path..\My work book.xls';

data myxls.table1;

set FreqCount;

run;

libname myxls clear;

Thank you!

[SASKiwi]

DDE to Excel is not possible in a SAS server environment not just for the lack of the X command. For DDE to work an instance of Excel must be open containing the workbook to be updated.

The Excel application cannot open in a remote server environment because there is no physical display for it to use.

If you can't use the LIBNAME EXCEL option then I think you are a bit stuck. ODS is not an option because it can't update an existing spreadsheet.

[LosEndos]

Thanks for your help guys.  I didn't really want to go down the DDE route if I could avoid it.

We have made a bit of progress on this, using the PCFILES libname engine approach as described by SASJedi here:

https://communities.sas.com/message/107761#107761

This involves creating named ranges on the target spreadsheet (as with the Excel libname) and writing to that range as if it were a table.  However, this doesn't entirely solve the problem as the variable names get written into the first row of the range (we need the cells above the data values to be blank without a column header) and we can't find any option that will switch this off (as I think there would be with the Excel libname). Have tried fudging this by attempting to set labels to blank and using labels instead of variable names but without success so far.

Any further suggestions would be most welcome.