DATA Step, Macro, Functions and more

Counting observations and subsetting into smaller datasets for Excel 97 exp

Reply
N/A
Posts: 1

Counting observations and subsetting into smaller datasets for Excel 97 exp

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
Contributor
Posts: 36

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

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;
Ask a Question
Discussion stats
  • 1 reply
  • 127 views
  • 0 likes
  • 2 in conversation