SAS Macro to put SAS data into Multiple Excel Work sheets

This post was kindly contributed by SASTechies - go there to comment and to read the full post.

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

This post was kindly contributed by SASTechies - go there to comment and to read the full post.

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….

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

This post was kindly contributed by SASTechies - go there to comment and to read the full post.

Post navigation

Post navigation