This sample program enables you to export more than 255 variables to an Excel workbook. The program, which runs in the third maintenance release of SAS^® 9.1 (TS1M3) or later, enables you to perform the following tasks:

• creating of a new workbook or additions to an existing workbook
• creating of as many worksheets as Excel will permit
• writing to either Excel XLS files (using the third maintenance release of SAS 9.1 (TS1M3) and SAS^® 9.2) or XLSX files (using SAS 9.2 (TS2M0) and later).
• using a set of common columns across all worksheets

All parameters for running the program are specified in the macro call at the bottom of the program. You should not have to make any changes in the actual program.

Note: Currently, the program is available for Windows environments that support the EXCEL engine.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

This program exports SAS data sets with more than 255 variables to Microsoft Excel workbooks by placing the variables in multiple worksheets in the workbook.

---

********************************************************************;
* Variable255.sas                                                  *;
* Purpose:  To split a SAS data set across multiple sheets         *;
*           via 255 columns at a time.                             *;
*                                                                  *;
* All changes to parameters for running this program should be made*;
* in the macro parameter calls that appear at the BOTTOM of this   *;
* program. You should not need to make any changes in the actual   *;
* code.                                                            *;
*                                                                  *;
* Version 1.10                                                     *;
* Date August 2009                                                 *;
* Restrictions - spaces or periods are not allowed in variable     *;
*                names. Variables cannot have the word "or" with   *;
*                blank spaces (for example, "My var yes or no ").  *;
*                However, you can use "or" without spaces          *;
*                ("Myvar yesorno")                                 *;
* All changes are done at the macro parameter list at the bottom   *;
* You should not have to change the program elsewhere              *;
* In version 9.2 TS2M0 on windows 32-bit  platforms and later this *;
*   program can also support XLSX files as well as XLS files       *;
********************************************************************;

options mprint macrogen symbolgen;


%macro split255(mylibref=,sasdsname=,tempdir=,tempds=,xlsname=,
                sheetname=,deleteit=,varcom=,comname=);
proc sql;
create table testcols as
select * from dictionary.columns
where libname=upcase("&mylibref") and memname=upcase("&sasdsname");
run;

data testcols2; set testcols;
keep name group ;
myobs=_n_;
group= (ceil(myobs/(255-&varcom))); 
run;

proc sql;
create table maxgroup as 
select max(group) as maxgroupvar from testcols2;
quit;

data setup; set maxgroup;
call symput('maxloop',maxgroupvar);
run; 

%put &maxloop;
run;  



* if we want to delete the .xls or .xlsx file before starting we do it here*;
%if &deleteit=destroy %then %do;
 systask command "del ""&xlsname"" ";
%end;


%macro loopit;
%do i = 1 %to &maxloop;
data subgroup&i; set testcols2;
where group=&i;
run;


%if &varcom=0 %then %do;
data _null_; set subgroup&i;
file "&tempdir&tempds&i..sas" ;
put name;
run; quit;
%end;

%if &varcom >=1 %then %do;
data subgroup&i;
set subgroup&i;
where name ^= "&comname";
run;

data setupcomname;
varcomnm=symget('comname');
varcom2=tranwrd(varcomnm,' or ',' '); 
file "&tempdir&tempds&i..sas" ;
put varcom2;
run; quit;


data _null_; set subgroup&i;
file "&tempdir&tempds&i..sas" mod;
put name;
run; quit;





%end;


data subset&i; 
retain 
%inc "&tempdir&tempds&i..sas";
;
set &mylibref..&sasdsname;
keep
%inc "&tempdir&tempds&i..sas";
;
run; quit;


proc export data=subset&i outfile="&xlsname" 
dbms=excel replace;
sheet="&sheetname&i";
run; quit;

%end;
%mend loopit;
%loopit;
%mend split255;
    
***********************************************************************************;
* The codes                                                                       *;
* MYLIBREF  the libref in the LIBNAME statement pointing to                       *;
*           the SAS datasets such as LIBNAME libref 'c:\mydirectory               *;
*                                                                                 *
* SASDSNAME the name of the SAS dataset that you are going to split               *;
*           the sas dataset is the sas7bdat file in the c:\mydirectory            *;
*                                                                                 *;
* TEMPDIR   this is the directory location where put code is located, it's where  *;
*           the variable list is written to load the individual worksheets        *;
*           the directory must exist                                              *;
*                                                                                 *;
* TEMPDS    the name of the temporary SAS programs created to load the individual *;
*           worksheets.  By default they are called SUBGROUP1 - SUBGROUPxx        *;
*                                                                                 *;
* XLSNAME   the name of the excel workbook we are going to create or update       *;
*           9.2 TS2M0 supports XLSX files but only 255 variables at a time        *;
*                                                                                 *;
* SHEETNAME the name of the individual worksheets - by default it will append     *;
*           a number on each one such as MYSHEET1, MYSHEET2, MYSHEET3 etc.        *;
*                                                                                 *;
* DELETEIT  whether to delete the excel workbook before starting, if you get an   *;
*           error of too many fields defined you can use this to delete the       *;
*           workbook before starting. Valid values are NO and DESTROY             *;
*                                                                                 *;
* VARCOM    whether to put common columns on each of the sheets for track         *;
*           0 does not put common columns on each of the sheets                   *;
*           a number greater than 1 will place that number of common columns      *;
*           on each of the worksheets.   For example, setting the value to 2      *;
*           will place 2 common columns on each worksheet                         *;
*                                                                                 *;
* COMNAME   the names of the common variables that you want on each work sheet    *;
*           you should place an OR parameter between multiple columns such as     *;
*           var1 or var2 or var3.  A single variable does not require an OR       *;
***********************************************************************************;


* This libname statement here is used in testing                  *; 
* put your LIBNAME statement pointing to your SAS dataset here    *;
* Then change the first two parameters to match your dataset      *;

libname fred 'c:\sastest';



%split255(mylibref=fred,             /* libref used to point to SAS dataset */               
          sasdsname=largevars,       /* sas data set name */
          tempdir=c:\sastest\,       /* temporary directory to store temporary code */
          tempds=subgroup,           /* temporary filename  */
          xlsname=c:\sastest\mysplitworkbook3.xls, /* name of the xls or xlsx (9.2) file */
          sheetname=mysheet,          /* name of the sheet */
          deleteit=destroy,                 /* delete the excel file before starting no or destroy */
          varcom=0,                    /* number of common variables 0, is none 1 or more */
          comname=obsno or colno );     /* common variable names to use on all the sheets */
		                               /* note the or between each one, make sure varcom */
		                               /* matches the number of variables in comname     */
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Excel workbook files (XLS files) have a limit of 255 variables per worksheet. This program enables you to export SAS data sets that have more than 255 variables to an Excel workbook by exporting the variables to multiple worksheets.

Type:	Sample
Topic:	Data Management ==> Data Sources ==> External Databases ==> MS Excel

Date Modified:	2009-10-21 13:06:27
Date Created:	2009-08-17 16:40:25

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS/ACCESS Interface to PC Files	Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3
		Microsoft Windows 2000 Server	9.1 TS1M3
		Microsoft Windows 2000 Professional	9.1 TS1M3
		Microsoft Windows NT Workstation	9.1 TS1M3
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3
		Microsoft Windows XP Professional	9.1 TS1M3