Dec 6, 2012

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:\);

2 comments:

  1. Hello there,
    Can I suggest to you to join
    sas-x.com
    ?

    Cheers,
    Tal

    ReplyDelete
  2. What a wonderful article? You have described very well. How you got this idea? Great work. Buy domain name

    ReplyDelete

Note: Only a member of this blog may post a comment.