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
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
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 ;
I did. I tried different styles: ' ' , [], () , "" (double quote to replace space) etc..
None of them worked.
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.
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.
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.
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.
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: