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
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
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 ?
@Shmuel I suppose I could supply them as variables from a datset
@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;
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.
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.
By using the option sheet_interval="none" both outputs are written to one sheet, but the given stating positions are ignored.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.