topic Re: Export SAS dataset with DDE: Excel file with worksheet name issue in ODS and Base Reporting

Export SAS dataset with DDE: Excel file with worksheet name issue

wtien196838 — Wed, 13 Apr 2016 23:21:13 GMT

I have export sas dataset to excel file using DDE.

The code has minor issue, I coded:

filename excelsys DDE 'EXCEL|<..path..>[file_name.xls]05 Quarter Performance!R1C6:R21C13' notab lrecl=6000 ;

if the tab name likes "Sheet1" without space, the code is OK. With the tab name likes above, it can not write the output and generate error message.

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.
Aborted during the EXECUTION phase.

Can you give me a hint to solve this issue. Thanks in advance.

Regards,

William

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

sophiaL — Thu, 14 Apr 2016 01:06:52 GMT

Hi William, I think you need to enclose the sheet name in single quotation marks when the sheet name has spaces in it:

filename excelsys DDE "EXCEL|<..path..>[file_name.xls]'05 Quarter Performance'!R1C6:R21C13" notab lrecl=6000 ;

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

wtien196838 — Thu, 14 Apr 2016 01:27:37 GMT

I did. I tried different styles: ' ' , [], () , "" (double quote to replace space) etc..

None of them worked.

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

Reeza — Thu, 14 Apr 2016 02:01:08 GMT

Try splitting your DDE reference.

Open workbook desired, then only need to reference sheet/cells.

I've done it so know it's possible but can't access my code until later.

See the example here, use X command to open file, then reference sheet mean.

http://www.ats.ucla.edu/stat/sas/faq/sas2excel_dde.htm

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

Reeza — Thu, 14 Apr 2016 02:17:23 GMT

No special treatment is necessary.

See the examples starting on Page 2.

So I think you have something else wrong. Post your full code please.

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

RW9 — Thu, 14 Apr 2016 08:21:15 GMT

More importantly, why are you using DDE to export to Excel? It is very old technology, only just supported, with old functionality and doesn't work in some setups? Try using tagsets.excelxp, export procedure, libname excel, or one of the many other far better methods to get data out to Excel - or in an ideal world don't use Excel in the first place.

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

wtien196838 — Thu, 14 Apr 2016 13:04:03 GMT

Reeza,

Here is the sample source code:

options noxwait noxsync ;

%let Excel_path = 'C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE' ;
Data _null_ ;
x = sleep(8);
run;

/* load template */

%sysexec "C:\temp\weekly_00000000.xls";

filename excelsys DDE 'EXCEL|C:\temp\[weekly_00000000.xls]05 Quarter Performance!R1C6:R21C13' notab lrecl=6000;

libname inpath1 'c:\temp\datasets';

Data _null_ ;
file excelsys ;
set inpath1.filein ;
put
    var1     '09'x
    var2     '09'x
    var3     '09'x
    var4      '09'x
    var5      '09'x
    var6     '09'x
    var7
    ;

run;
filename cmds dde 'excel|system' ;
data _null_ ;
file cmds; ;
put '[SELECT("R1C6:R21C13")]';
put '[SAVE.AS("C:\temp\weekly_20160331.xls")]'; ;
* put '[QUIT()]';
run;

filename excelsys clear;

------------------------------------------------------------------------------------------------------------

PS: this is one of worksheets that user request to add in for a report of 15 worksheets.

The old code is so complex that I just want to replace my new code. If there is no way, I need to investigate the old code to put my code in.

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

Reeza — Thu, 14 Apr 2016 16:25:47 GMT

If you already have the file open, your last file name statement should be ONLY sheet and cell reference, not the full path again.

Again, see the paper pg 2 for an example.

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

wtien196838 — Thu, 14 Apr 2016 19:11:19 GMT

Thank you for all helpful suggestions.

Reeza suggested me a hint: remove the path of excel output file (in filename). Then sas export data output correctly into cell range and into correct tabname (with space).

Again thanks for all contributors.

Regards,

William