BookmarkSubscribeRSS Feed
BCNAV
Quartz | Level 8

We have the following program here:

 

*******************************************************************************;
* 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;

x "'C:\Program Files (x86)\Microsoft Office\Office14\excel.exe'";

data _null_;
x=sleep(4);
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(d:\egtask,    /* directory where sas dataset lives */
         egtask,          /* libref associated with the dataset, do not use test*/
         FCST_WINTERS_AUTO_OV_ECU_M11,           /* name of the sas dataset */
         R:\Traffic Forecasting\TFM 3.0\,    /* location of the final xls file */
         Forecast Input Data.xlsx,       /* name of the final xls file */
         HW_AUTO,            /* sheet name in the xls file */
         1,                 /* starting row */
         1,                /* 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*/
         clear,               /* 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 */ 

 

This works perfectly to export to Excel at a specific range. I'd like to be able to put a macro outside of it to loop on:

 

FCST_WINTERS_AUTO_OV_ECU_M11, /* name of the sas dataset */

HW_AUTO, /* sheet name in the xls file */

 

The above two lines should be dynamic. That is, the name of the sas dataset needs to be passed, as well as the sheet name.

 

Can there be a macro outside of the main macro to pass these names? As models are added, variables ahd sheet names would be added, hopefully simply.

 

Thanks!

-Bill

11 REPLIES 11
Reeza
Super User
This uses DDE and is really old. There are better ways to export to Excel if that's all you're looking to accomplish. If you're creating an Excel file, ODS EXCEL is really powerful and can automate your whole process. In 2006, this likely would have been the best method, but it is over a decade later now.

The only rationale for using this type of methodology is if you're using very rigid templates that you need to work with and basically to not change things. But if you're looking to expand this process, now may be a good time to redesign it using the 'modern' day methods.
Reeza
Super User
Another option though is CALL EXECUTE to call your macro, rather than a macro in a macro. The documentation has examples on using a dataset to drive a macro.
BCNAV
Quartz | Level 8

To be clear, this macro was provided to us by SAS support, so I hesitate to change it to ODS. I will try to figure out call execute, but I am not sure about that, at least for nopw

Reeza
Super User
And it's a perfectly valid solution for 2006.
Shmuel
Garnet | Level 18

This two arguments are already dynamic - I have marked them by /*>>>*/ in front of line.

You execute the macro by:

 

%rangeexp(d:\egtask,    /* directory where sas dataset lives */
         egtask,          /* libref associated with the dataset, do not use test*/
/*>>>*/         FCST_WINTERS_AUTO_OV_ECU_M11,           /* name of the sas dataset */
         R:\Traffic Forecasting\TFM 3.0\,    /* location of the final xls file */
         Forecast Input Data.xlsx,       /* name of the final xls file */
/*>>>*/         HW_AUTO,            /* sheet name in the xls file */
         1,                 /* starting row */
         1,                /* 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*/
         clear,               /* 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 */ 

How do you want to supply the list of values for a loop ?

 

BCNAV
Quartz | Level 8

@Shmuel I suppose I could supply them as variables from a datset

Shmuel
Garnet | Level 18

@BCNAV wrote:

@Shmuel I suppose I could supply them as variables from a datset


I understand that you are going to use modern methods to deal with xlsx files. That's good.

 

Anyway, suppose you want to submit any macro from a datastep, supplying some arguments from 

an arguments dataset, then use next skeleton:

Data _NULL_;
        retain text1 "<macro start string upto 1st argument to change>"
                  text2 "<macro fixed arguments in between>"  /* add as many lines you need */
                  text3 "<macro final arguments and macro execution>"
          ;
     set <arguments> end=eof; 
           cmd = catx(',',text1, arg1, text2, arg2, text3);
           call execute(cmd);
run;     

alternatively, supose your macro is defined with named arguments, like:

%macro macro_name(arg1=text1, arg2=text2, ..., argn=textn);
    ... macro code ....
%mend;

then your skeleton may be more simple:

Data _NULL_;
        retain text1 "<macro start string with all unchangeable arguments>"                  
               text2 "<macro close and execute>"
          ;
     set <arguments> end=eof; 
arg1 = <arg1_name = > || trim(arg1_vaeiable);
arg2 = <arg2_name = > || trim(arg2_vaeiable);
cmd = catx(',' ,text1,arg1,arg2,...,text2);
call execute(cmd);
run;
Patrick
Opal | Level 21

@BCNAV

The moment you/your organisation changes to a client-server topology DDE won't work anymore. 

http://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/ 

 

Change if you can. This would also allow to simplify the code and make it better maintainable. 

 

Reeza
Super User
And a modern day version of this approach, but no DDE - http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

I think it does use VBS though in the background.
BCNAV
Quartz | Level 8

I can certainly see how others prefer ODS Excel, and I suspect that it is better and more future proof. I just need to convice others.

 

The output is actually quite easy:

 

ods excel file="C:\data\test.xlsx" options(start_at="1,1" sheet_name="OV_M11_HWA_EST");
	proc print data=EGTASK.EST_WINTERS_AUTO_OV_ECU_M11; run;
	ods excel options(start_at="1,1" sheet_name="OV_M11_HWA_FCST");
	proc print data=EGTASK.FCST_WINTERS_AUTO_OV_ECU_M11; run;
ods excel close;


This way I can provide forecast model parameters (EST) and forecasts (FCST) as separate sheets, which is pretty easy. I then tried to have estimates and output on the same sheet, but I do not think ODS likes it. The below will create a copy of the worksheet instead of just plopping in the output:

 

 

ods excel file="C:\data\test.xlsx" options(start_at="1,1" sheet_name="OV_M11_HWA_EF");
	proc print data=EGTASK.EST_WINTERS_AUTO_OV_ECU_M11; run;
	ods excel options(start_at="6,1" sheet_name="OV_M11_HWA_EF");
	proc print data=EGTASK.FCST_WINTERS_AUTO_OV_ECU_M11; run;
ods excel close;

Can this be done?

 

 

I'll get there I hope.

andreas_lds
Jade | Level 19

By using the option sheet_interval="none" both outputs are written to one sheet, but the given stating positions are ignored.

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
  • 11 replies
  • 978 views
  • 2 likes
  • 5 in conversation