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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.