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