SAS Macro to put SAS data into Multiple Excel Work sheets

I believe most of us have encountered this situation...
I have a sas dataset with more than 70k records...How can I provide this information in an Excel file to the business user....


If one is using previous versions of MS Excel ie. < Excel 2007 then one can provide the information in multiple excel sheets in an Workbook...or put it to ,(comma) or a | (pipe) delimited file and then open that with notepad/word application.... 

if you still wanted to stick with Excel format...then split the dataset to ~65000 rows and load it multiple work sheets in a workbook....

Try with this macro example where I have created a dataset with 1 million records.....This macro then splits the dataset at 65000 records and then dumps the data to multiple worksheets in the Example.xls file....

ExcelXP tagsets are used here that allow us to write to multiple worksheets in a work book....

Inorder to try this example you will have to download this zip file from SAS @ http://support.sas.com/rnd/base/ods/odsmarkup/tagsets_9.1.zip

and then find and extract excltags.tpl file and put it at your C:\ or a location of your choice (In that case please update your location at this statement %include "C:\excltags.tpl"; )


Here's the code then....

options nosource nomprint nomlogic nosymbolgen;

/**

Usage of the Macro-

%splitdsnbyobs(DatasetName, No ofobservation to split by)

**/

/* creating a dataset with 100000 observations*/

%macro splitdsnbyobs(dsn,splitby,ExlFilename,ExlFilepath);

data _null_;

      set &dsn nobs=num;

      call symput('no_obs',num);

run;

%let no_obs=&no_obs; /*Get the number of observations in &dsn*/

%include "C:\excltags.tpl";

ods listing close;

ods tagsets.excelxp path="&ExlFilepath" file="&ExlFilename" style=printer

      options( embedded_titles='yes' embedded_footnotes='yes' suppress_bylines='no');

/* keep the observations from firstobs= and obs=*/

%do i=1 %to %sysfunc(ceil(&no_obs/&splitby));

      data &dsn.&i.;

      set &dsn (firstobs=%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) obs=%sysfunc(ceil(%eval(&i * &splitby.))));

      run;

      ods tagsets.excelxp options(sheet_interval='none' sheet_name="&dsn.&i.");

      proc print data=&dsn.&i.; run;

%end;

ods tagsets.excelxp close;

ods listing;

%mend splitdsnbyobs;

/* Eg. Create a Dsn with 1000000 observations */

data loops;

do i=1 to 1000000;

      output;

end;

run;

/*Now call the macro to split the observations every 65000 records*/

%splitdsnbyobs(loops,65000,Example.xls,C:\);

Some useful links on this topic


http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html
http://support.sas.com/rnd/base/ods/odsmarkup/
http://support.sas.com/rnd/base/ods/odsmarkup/tagsets_9.1.zip
http://support.sas.com/kb/32/394.html
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html