topic Re: Using ODS to send to EXCEL in ODS and Base Reporting

Using ODS to send to EXCEL

lloraine — Thu, 06 Dec 2007 16:50:02 GMT

I am trying to create an Excel file using ODS. My file contains 67647 rows. My Excel file is truncated at 65536 rows. Is there anyway to make the overflow go to the next tab in Excel??

Re: Using ODS to send to EXCEL

Cynthia_sas — Thu, 06 Dec 2007 23:16:17 GMT

Hi:
Two easy things to do are
1) use FIRSTOBS= and OBS= to split up the data using TAGSETS.EXCELXP to create the multiple sheets (you could even "macroize" whether the data needed to be split up or not, but the program below shows a simple example, not a macroized one).
OR
2) use BY group processing to break the data into logical groups -- TAGSETS.EXCELXP will put every group into a separate sheet, by default. (of course, this assumes that no single by group is larger than the 65K limit)

cynthia
[pre]
**1) use FIRSTOBS= and OBS=;
ods tagsets.excelxp file='c:\temp\split_file.xls';

proc print data=sashelp.class(firstobs=1 obs=7);
run;

proc print data=sashelp.class(firstobs=8 obs=19);
run;

ods tagsets.excelxp close;

**2) Split into BY groups;
ods tagsets.excelxp file='c:\temp\split_by.xls';

proc print data=sashelp.shoes;
where region in ('Asia', 'Canada', 'Pacific');
by region;
var region subsidiary product sales;
run;

ods tagsets.excelxp close;
[/pre]

Re: Using ODS to send to EXCEL

lloraine — Thu, 06 Dec 2007 23:49:00 GMT

If I use the following code to export into ACCESS is there any problem with the number of rows of data?

PROC EXPORT DATA = PS_CLASS_TBL
OUTTABLE = "PS_CLASS_TBL"
DBMS = ACCESS REPLACE;
DATABASE = "C:\CRSE_FACULTY_GRADUATE.MDB";
QUIT;

Re: Using ODS to send to EXCEL

Cynthia_sas — Fri, 07 Dec 2007 05:13:55 GMT

Hi:
I don't know the exact answer to that. According to this site:
http://office.microsoft.com/en-us/access/HA100307391033.aspx
the limit for Microsoft Access tables is based on overall file size, not number of rows. (no more than 255 fields in the table with an overall file size of 2 gigabytes, minus the space needed for system objects).

You may want to run a test or double check with Tech Support on this one.

cynthia