*******************************************************************************;
* Rangeexporter9                                                              *;
* Purpose:  to be able export sas data to any range location in excel         *;
* Date:  January 2006                                                         *;
* This program can also be used with folks who want to export using DDE       *;
* Modifications                                                               *;
* 4/11/2006 - added a routine to handle row calls greater than 99 cells       *;
*******************************************************************************;

* Start the macro here *;


dm wpgm 'clear log' wpgm;
options noxwait noxsync;
%macro rangeexp(directory,libref,dsname,finaldir,finalname,sheetname,row,column,varnames,
                dblabel,xlsstate,replacesheet,sheetstate);

* launching excel here *;

%let xlsname=&finaldir&finalname;

options noxwait noxsync;
filename sas2xl dde 'excel|system';
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+20;
    do while (fid le 0);
      fid=fopen('sas2xl','s');
      time=datetime();
      if (time ge stop) then fid=1;
      end;
    end;
  rc=fclose(fid);
run;


%let ext1=%str(.xls);
%let outfile1=&directory&dsname&ext1;

*****************************************************;
* Setting up the libname for the sas dataset here   *;
* to extract the information about it               *;
*****************************************************;

libname &libref "&directory";

* if the final xls file does not exist we need to create it *;
* this is turned on with xlsstate set to new                *;

%if &xlsstate=new %then %do;
filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[save.as("'"&xlsname"'")]';
put '[close("false")]';
run; quit;
%end;  * if the final xls file is a new file *;

* if the sheet does not exist then we create it here and clear it*;

%if &sheetstate=new %then %do;
proc export data=sashelp.class outfile="&xlsname";
sheet="&sheetname";
run;
%end; * If the sheet does not exist *;





***********************************************************;
* this routine will clear the cells in the workbook       *;
* this is turned on by setting the replace sheet to clear *;
***********************************************************;
%if &replacesheet=clear %then %do;
filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[open("'"&xlsname"'")]';
put '[workbook.activate("'"&sheetname"'")]';
put '[select("r1c1:r65536c256")]';
put '[error("false")]';
put '[clear(1)]';
put '[error("false")]';
put '[save()]';
put '[select("r1c1")]';
put '[error("false")]';
put '[save()]';
put '[close("false")]';
run;
%end; * Clear cells routine *;


options mprint macrogen symbolgen;
libname test "&directory";
ods trace on;
run;


****************************************************************;
* extracting the variables and the number of observations here *;
****************************************************************;

ods listing close;
ods output "Variables"=varvol1 "Attributes"=attr1;
proc contents data=&libref..&dsname;
run; quit;
ods output close;
ods listing;

* Now we figure out the total number of observations including *:
* the starting row in excel                                    *;
* the addition of row allows us to set the ending target cells *;

data attr2;
set attr1;
if Label2="Observations";
keep Label2 Cvalue3;
cvalue3=cvalue2 + (&row - 1);
run; quit;

*this new routine allows for values longer than 2 digits to be used *;

data attr2; set attr2;
rename cvalue3=cvalue2;
run; quit;


****************************************************************;
* These little routines figure out the number of variables     *;
* in the sas dataset itself                                    *;
****************************************************************;

data varvol2; set varvol1;
varno=1;
run;

proc means data=varvol2 sum;
var varno;
output out=varvol3;
run;

data varvol4; set varvol3;
if _STAT_='N';
keep _stat_ varno;
varno=(left(trim(varno)));
varno3=varno;
varno=varno + (&column - 1);
call symput("initcolumn",(left(trim(varno3))));
run;


*********************************************************************;
* This is where we  build the dde code                              *;
* put statements on the fly                                         *;
* this took some interesting coding                                 *;
*********************************************************************;

%let variables=%str(variables.txt);
%let varheader=%str(varheader.txt);

%if &dblabel=no %then %do;
data varvol1; set varvol1;
quoter="'";
variable2=(left(trim(variable)));
variable3=quoter||variable2||quoter;
variable3=compress(variable3); 
variable4=variable3 || '&tab';
run;
%end;


%if &dblabel=yes %then %do;
data varvol1; set varvol1;
quoter="'";
spacer='20'x;
variable2=(left(trim(label)));
variable2=translate(label,"","'");
variable3=quoter||variable2||quoter; 
variable4=variable3 || spacer || '&tab';
run;
%end;


proc sort; by num;
run;

****************************************;
* first 3 crate the data put statement *;
****************************************;

data _null_; 
file "&directory&variables";
put 'put' ;
run;

data _null_; set varvol1;
file "&directory&variables" mod;
put variable;
run;
quit;

data _null_; 
file "&directory&variables" mod;
put ';' ;
run;
quit;

***************************************************;
* 2nd 3 create the variable hearder put statement *;
***************************************************;

data _null_; 
file "&directory&varheader";
put 'put' ;
run;

data _null_; set varvol1;
file "&directory&varheader" mod;
put variable4;
run;
quit;

data _null_; 
file "&directory&varheader" mod;
put ';' ;
run;
quit;

*******************************************************************;
* Varnames = yes cells > 500 dde put routines calling both header *;
* and data both as put calls                                      *;
*******************************************************************;

%if &varnames=yes %then %do;

%let tab='09'x;

data attr2; set attr2;
cvalue2=cvalue2 + 1;
row2=&row + 1;
run;



data setup1; set varvol4;
call symput("numvars",left(trim(varno)));
run;

data setup2; set attr2;
call symput("numobs",left(trim(Cvalue2)));
call symput("row2",left(trim(row2)));
run;

%let beginr=r&row;
%let beginr2=r&row2;
%let beginc=c&column;
%let beginheader=&beginr&beginc:;
%let beginf=&beginr2&beginc:;

%let end=r&numobs;
%let end2=c&numvars;
%let endheader=&beginr&end2;
%let endrange=&end&end2;
%let select1=&beginf&endrange;
%let selecthdr=&beginheader&endheader;
%let xlsname=&finaldir&finalname;
%let tab='09'x;

filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[open("'"&xlsname"'")]';
put '[workbook.activate("'"&sheetname"'")]';
run; quit;


data _null_; set varvol1; 
%let filestr=%str(filename ddedata dde "excel|&finaldir[&finalname]&sheetname!&beginheader&endheader");
	%put &filestr;
&filestr;
file ddedata notab lrecl=16084;
%inc "&directory&varheader";
run;



data _null_; set &libref..&dsname; 
%let filestr=%str(filename ddedata dde "excel|&finaldir[&finalname]&sheetname!&beginf&endrange");
	%put &filestr;
&filestr;
file ddedata notab dlm='09'x lrecl=16084;
%inc "&directory&variables";
run;

data _null_;
file cmds;
put '[save()]';
put '[quit()]';
run; 



%end; *varnames yes in greater loop to set range, ignore no varnames comment *;


*******************************************************;
* calling varnames = no cells > 500 routine           *;
* this one does not write out the header              *;
*******************************************************;

%if &varnames=no %then %do ;

data setup1; set varvol4;
call symput("numvars",left(trim(varno)));
run;

data setup2; set attr2;
call symput("numobs",left(trim(Cvalue2)));
run;

%let beginr=r&row;
%let beginc=c&column;
%let beginf=&beginr&beginc:;
%let end=r&numobs;
%let end2=c&numvars;
%let headend=&beginr&end2;
%let endrange=&end&end2;
%let select1=&beginf&endrange;

%let xlsname=&finaldir&finalname;

filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[open("'"&xlsname"'")]';
put '[workbook.activate("'"&sheetname"'")]';
run; quit;

data _null_; set &libref..&dsname; 
%let filestr=%str(filename ddedata dde "excel|&finaldir[&finalname]&sheetname!&beginf&endrange");
	%put &filestr;
&filestr;
file ddedata notab dlm='09'x lrecl=16084;
%inc "&directory&variables";
run;

data _null_;
file cmds;
put '[error(false)]';
put '[save.as("'"&xlsname"'")]';
put '[quit()]';
run; 

%end; * no varnames cells greater than 500 *;

%mend rangeexp;

*********************************************************************;
* now we call the macro here                                        *;
* 1. Directory where sas dataset lives                              *;
* 2. Libref you want to use with original dataset                   *;
*    note that you may need to set this up,  do not use test        *;
*    as your libref as I am using that :)                           *;
* 3. Name of original sas dataset                                   *;
* 4. Directory of the final xls you are writing to                  *;
* 5. name of the xls file you are writing to                        *;
* 6. name of the sheet you are writing to                           *;
* 7. the row you want to start in                                   *;
* 8. the column you want to start in                                *;
* 9. whether you want the variable names to be written in the       *;
*    first row of the data                                          *;
* 10. Whether to use labels or not, note if your dataset does not   *;
*     have labels you might get periods instead, note that you      *;
*     must set parameter 8 to yes as well                           *;
* 11. Whether to create a new xls file or update an existing one    *;
*     switches are new and exist                                    *;
*     If you are creating a new xls file set the next parameter to  *;
*     CLEAR and the final parameter to NEW                          *;
*     Except when the sheet name is sheet1, sheet2, or sheet3       *;
* 12. Tells excel to clear the contents of the sheet before writing *;
*     Clear tells it to clear the contents                          *;
*     No tells it not to clear the contents                         *;
* 13. does the sheet exist already ? If it does not set the         *;
*     parameter to new, and set the previous parameter to clear     *;
*     note if the sheet name is sheet1, sheet2, or sheet3 and       *;
*     the xls file is new in parameter 11 then leave this as exist  *;
*********************************************************************;

%rangeexp(c:\testsas\,    /* directory where sas dataset lives */
         sashelp,          /* libref associated with the dataset, do not use test*/
         class,           /* name of the sas dataset */
         c:\testsas2\,    /* location of the final xls file */
         test7.xls,       /* name of the final xls file */
         sheet1,            /* sheet name in the xls file */
         5,                 /* starting row */
         3,                /* starting column */
         yes,              /* write out variable names, yes or no */
         no,               /* write out variable labels, yes or no, varnames must be yes too */
         exist,              /* does the xls file exist ? , exist or new,  if new set clear and new for next 2 parmeters*/
         no,               /* clear all existing cells in the sheet. clear or no */  
         exist      );     /* does the sheet itself exist? new (turn on clear above) or exist  */ 
                           /* note that if xls is new and the sheet name is sheet1, sheet2, or sheet3 */
                           /* then the sheet exists because these are the default sheet names */