The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-14-2016 03:11 PM
Contributor
Posts: 56

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

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

View solution in original post


All Replies
Regular Learner
Posts: 1

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

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 ;

Contributor
Posts: 56

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

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

None of them worked. 

Super User
Posts: 17,840

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

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

Super User
Posts: 17,840

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

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.

Super User
Super User
Posts: 7,404

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

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.

Contributor
Posts: 56

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

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.

Super User
Posts: 17,840

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

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. 

Solution
‎04-14-2016 03:11 PM
Contributor
Posts: 56

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 724 views
  • 0 likes
  • 4 in conversation