Counting observations and subsetting into smaller datasets for Excel 97 exp

43432 — Mon, 04 May 2009 21:55:22 GMT

I was wondering if anybody knows how to fix the following:

I have a large datasets which updates every week so the total observation count is varying. Because I want to export to Excel 97 which can only handle 65,000 records I need to cut the SAS datasets into smaller pieces depending on the total observational count.

For example if the SAS datasets contains 325,000 observations I would like SAS to create 325,000/65,000=5 subsets of the large datasets.

Does anyone know how to do this?

Regards,

Rishi

Re: Counting observations and subsetting into smaller datasets for Excel 97 exp

barheat — Tue, 05 May 2009 02:45:57 GMT

Rishi:

The first step is to get the record count into a macro variable. The easiest way is an SQL statement.

proc sql noprint;
select count(*) into :nobs
from dsname;
quit;

Next step would be to determine the number of files to create:

data temp;
count=int(&nobs/65000) + 1;
call symputx('count',count);
run;

Macro variable &count contains the number of tables to create. You can use this in a macro do loop.

%macro xlcreate;
%do i=1 %to &count;
data temp;
set dsname;
obs=_n_;
if ((&i-1)*65000 + 1) le obs le &i*65000;
run;

/* use proc export, tagsets.excelxp or some other method to create an excel file, Use the &i macro variable to create file name versions */
proc export data=temp ...

%end;
%mend;

topic Re: Counting observations and subsetting into smaller datasets for Excel 97 exp in SAS Programming

Counting observations and subsetting into smaller datasets for Excel 97 exp

Re: Counting observations and subsetting into smaller datasets for Excel 97 exp