BookmarkSubscribeRSS Feed
LosEndos
Calcite | Level 5

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.

7 REPLIES 7
Linlin
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Onyx | Level 15

LinLin,

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

Regards,

Haikuo

Lida
Obsidian | Level 7

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
PROC Star

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

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 6332 views
  • 0 likes
  • 5 in conversation