topic Re: DDE Error in ODS and Base Reporting

DDE Error

raveena — Fri, 18 Mar 2011 14:00:05 GMT

Hi ,

I am getting an error while exporting an data using DDE. The steps taken to resolve this issue:
1. Opened an new sas session
2. Closed the excel sheet and ran the program.
3. Opened the excel sheet & minimized and ran the program

But still am getting an error as below,
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data step program.Aborted during the EXECUTION phase.

Please let me know how to resolve this issue.

Thanks in Advance

Re: DDE Error

Andre — Fri, 18 Mar 2011 14:05:51 GMT

In order to obtain a help, you need to show your entire code and log

Andre

Re: DDE Error

Cynthia_sas — Fri, 18 Mar 2011 14:11:28 GMT

You do not need to post the same question in 2 separate forums, especially since you do NOT use ODS when you are using DDE.

A suggestion has been posted at your other posting site:
http://support.sas.com/forums/thread.jspa?messageID=52398첮

cynthia

Re: DDE Error

raveena — Fri, 18 Mar 2011 14:22:36 GMT

Hi Andre,

Here is the code

%macro dde_openExcel;
options noxsync noxwait;
filename sas2xl dde 'excel|system'; * reference toopen/closeexcel;
data _null_;
length fid rc start stop time 8;fid=fopen('sas2xl','s');
if (fid le 0) then do;rc=system('start excel');
start=datetime();stop=start+10;
do while (fid le 0);fid=fopen('sas2xl','s');
time=datetime();if (time ge stop) then fid=1;end;end;
rc=fclose(fid);run;
%mend dde_openExcel;

%macro dde_makeWorkbook(thepath=,thefile=);
filename sas2xl dde 'excel|system';
filename DDEcmds dde 'excel|system';
data _null_;
file sas2xl;put '[error(false)]';
put %unquote(%bquote('[save.as("&thepath.\&thefile")]')); * save as ;run;
%mend dde_makeWorkbook;

%macro dde_delvars;
data vars; set sashelp.vmacro; run;
data _null_;set vars;
if scope='GLOBAL' and substr(name,1,9)='VARHEADER' then
call execute('%symdel '||trim(left(name))||';'); run;
data _null_;set vars;
if scope='GLOBAL' and substr(name,1,9)='VARLIST' then
call execute('%symdel '||trim(left(name))||';');run;
%mend dde_delvars;

%macro dde_getRowsColsLabels(dsn=);%global nvar nobs ngroups;
proc contents data=&dsn noprint out=&dsn._con; run;
proc sql noprint;
select max(varnum), max(nobs) into : nvar, : nobs from &dsn._con;quit;
%let nvar = %left(&nvar);
%let nobs = %left(&nobs);
%let vargroup = %eval(&nvar / 10);
%let vargroups = %eval(&vargroup + 1);
%let ngroups = %left(&vargroups);
%do i = 1 %to &ngroups;
%let nvarmax = %eval(&i * 10);
%let nvarmin = %eval(&nvarmax - 10 + 1);
%global varheader&i varlist&i;

proc sql noprint;
select labelinto :varheader&i separated by '09'xfrom &dsn._con
where &nvarmin <= varnum <= &nvarmax order by varnum; quit;
proc sql noprint;
select name into :varlist&i separated by " '09'x "from &dsn._con
where &nvarmin <= varnum <= &nvarmax order by varnum; quit; %end;
%mend dde_getRowsColsLabels;

* Write header and data records from SAS to Excel ;
%macro dde_writeToX(workbook=,worksheet=,dsn=);
%do i = 1 %to &ngroups;
%let nvarmax = %eval(&i * 10);
%let nvarmin = %eval(&nvarmax - 10 + 1);filename ddedata dde "excel|[&workbook.]&worksheet.!r1c&nvarmin.:r1c&nvarmax" notab;
data _null_;file ddedata; put "&&varheader&i";run;
%let nobsplus1 = %eval(&nobs + 1);
filename ddedata dde "excel|[&workbook.]&worksheet.!r2c&nvarmin.:r&nobsplus1.c&nvarmax" notab;
data _null_;file ddedata;set &dsn;put &&varlist&i;run;
%end;
%mend dde_writeToX;

%macro dde_closeSave(thepath=,thefile=);
data _null_;file sas2xl;
put '[error(false)]';
put %unquote(%bquote('[save.as("&thepath.\&thefile")]')); * save as ;put '[file.close("true")]'; * close the file [true=save / false=do not save] ;
put '[quit()]';run;
%mend dde_closeSave;options noxwait noxsync;

* dde processing;
%macro dde_process(st=, dsn=, file=);
%let path = &drv7.\&st.\&project.\current;
%dde_openExcel;data test; x=sleep(3);run;
%dde_makeWorkbook(thepath=&path, thefile=&file) ;
%dde_delvars;
%dde_getRowsColsLabels(dsn=&dsn);
%dde_writeToX(workbook=&file,worksheet=Sheet1,dsn=&dsn);
%if "&file" NE "&project._00_E.xls" and "&file" NE "&project._00_F.xls" %then %do;
%formatReportDetail(dsn=&dsn, worksheet=Sheet1);%end;
%if "&file" EQ "&project._00_E.xls" %then %do;
%formatReportVIPSummary(dsn=&dsn, worksheet=Sheet1);%end;
%if "&file" EQ "&project._00_F.xls" %then %do;
%formatReportRepProductivity(dsn=&dsn, worksheet=Sheet1);%end;
%dde_closeSave(thepath=&path, thefile=&file);
%mend dde_process;
/*Report E*/
%dde_process(st=NY, dsn=VIPsumNY,file=&project._00_E.xls)
/*Report F*/
%dde_process(st=NY, dsn=mhs2, file=&project._00_F.xls)

Thanks,
Raveena

Message was edited by: raveena

Message was edited by: raveena

Message was edited by: raveena Message was edited by: raveena

Re: DDE Error

raveena — Fri, 18 Mar 2011 14:44:53 GMT

Code continues:

proc sql noprint;
select labelinto :varheader&i separated by '09'xfrom &dsn._con
where &nvarmin <= varnum <= &nvarmax order by varnum; quit;
proc sql noprint;
select name into :varlist&i separated by " '09'x "from &dsn._con
where &nvarmin <= varnum <= &nvarmax order by varnum; quit; %end;
%mend dde_getRowsColsLabels;

* Write header and data records from SAS to Excel ;
%macro dde_writeToX(workbook=,worksheet=,dsn=);
%do i = 1 %to &ngroups;
%let nvarmax = %eval(&i * 10);
%let nvarmin = %eval(&nvarmax - 10 + 1);filename ddedata dde "excel|[&workbook.]&worksheet.!r1c&nvarmin.:r1c&nvarmax" notab;
data _null_;file ddedata; put "&&varheader&i";run;
%let nobsplus1 = %eval(&nobs + 1);
filename ddedata dde "excel|[&workbook.]&worksheet.!r2c&nvarmin.:r&nobsplus1.c&nvarmax" notab;
data _null_;file ddedata;set &dsn;put &&varlist&i;run;
%end;
%mend dde_writeToX;

%macro dde_closeSave(thepath=,thefile=);
data _null_;file sas2xl;
put '[error(false)]';
put %unquote(%bquote('[save.as("&thepath.\&thefile")]')); * save as ;put '[file.close("true")]'; * close the file [true=save / false=do not save] ;
put '[quit()]';run;
%mend dde_closeSave;options noxwait noxsync;

* dde processing;
%macro dde_process(st=, dsn=, file=);
%let path = &drv7.\&st.\&project.\current;
%dde_openExcel;data test; x=sleep(3);run;
%dde_makeWorkbook(thepath=&path, thefile=&file) ;
%dde_delvars;
%dde_getRowsColsLabels(dsn=&dsn);
%dde_writeToX(workbook=&file,worksheet=Sheet1,dsn=&dsn);
%if "&file" NE "&project._00_E.xls" and "&file" NE "&project._00_F.xls" %then %do;
%formatReportDetail(dsn=&dsn, worksheet=Sheet1);%end;
%if "&file" EQ "&project._00_E.xls" %then %do;
%formatReportVIPSummary(dsn=&dsn, worksheet=Sheet1);%end;
%if "&file" EQ "&project._00_F.xls" %then %do;
%formatReportRepProductivity(dsn=&dsn, worksheet=Sheet1);%end;
%dde_closeSave(thepath=&path, thefile=&file);
%mend dde_process;

/*Report E*/
%dde_process(st=NY, dsn=VIPsumNY, file=&project._00_E.xls)
/*Report F*/
%dde_process(st=NY, dsn=mhs2, file=&project._00_F.xls) Message was edited by: raveena

Re: DDE Error

Andre — Fri, 18 Mar 2011 15:02:31 GMT

Raveena

Strange dde as i don't see any data step reading or writing upon an excel sheet
but i can reproduce your error

[pre]
36 %let thepath=d:\temp; %let thefile=raveena.xls;
37 %dde_makeWorkbook ;
MPRINT(DDE_MAKEWORKBOOK): filename sas2xl dde 'excel|system';
MPRINT(DDE_MAKEWORKBOOK): filename DDEcmds dde 'excel|system';
MPRINT(DDE_MAKEWORKBOOK): * reference for spreadsheet manipulation;
MPRINT(DDE_MAKEWORKBOOK): data _null_;
MPRINT(DDE_MAKEWORKBOOK): file sas2xl;
MPRINT(DDE_MAKEWORKBOOK): put '[error(false)]';
MPRINT(DDE_MAKEWORKBOOK): put '[save.as("\")]';
MPRINT(DDE_MAKEWORKBOOK): * save as ;
MPRINT(DDE_MAKEWORKBOOK): run;

NOTE: Le fichier SAS2XL est :
Session DDE,
SESSION=excel|system,RECFM=V,LRECL=256

ERROR: Session DDE n'est pas prête.
FATAL: Erreur d'E/S irrécupérable détectée lors de l'exécution de l'étape DATA.
Interrompu pendant la phase EXECUTION.
[/pre]

ilf i submit after
[pre]
%symdel thepath;
%symdel thefile;

%macro dde_makeWorkbook(thepath=,thefile=);
filename sas2xl dde 'excel|system';
filename DDEcmds dde 'excel|system'; * reference for spreadsheet manipulation;
data _null_;
file sas2xl;
put '[error(false)]';
put %unquote(%bquote('[save.as("&thepath.\&thefile.")]')); * save as ;
run;
%mend dde_makeWorkbook;

%dde_makeWorkbook(thepath=d:\temp,thefile=raveena.xls) ;
[/pre]

then there is no error

Are knowing macro language or only applying a code made by someboty else?

The suggestion of Cynthia was : direct to technical support

Andre