BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wtien196838
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
wtien196838
Quartz | Level 8

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

8 REPLIES 8
sophiaL
Calcite | Level 5

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 ;

wtien196838
Quartz | Level 8

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

None of them worked. 

Reeza
Super User

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

Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

wtien196838
Quartz | Level 8

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.

Reeza
Super User

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. 

wtien196838
Quartz | Level 8

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

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
  • 8 replies
  • 3352 views
  • 0 likes
  • 4 in conversation