Group and export data

[GS2]

Hello,

Using SAS 9.4

I have 61,300 records and I want to export to excel (as a csv) every 500 records (500 is the max to import for another program). Is there an efficient way to program this so I do not have to manually export 120+ files? Thank you 

[Reeza]

Add a proc export to this program:
https://gist.github.com/statgeek/abc3c6ce1dbeedb84fe7f11da0603cda

[GS2]

Will I have to add a proc export for each set of 500 observations?

[jimbarbour]

If you add the Proc Export to the macro, you should not have to code multiple Proc Exports.  I think right after the data step would be a good spot to add the Proc Export.

Jim

    %do i=1 %to &nfiles;
    
        %*Split file by number of records;
        data &outDsnPrefix.&i.;
            set &dsn. (firstobs=&first obs=&last);
        run;

        %*Increment counters to have correct first/last;
        %let first = %eval(&last+1);
        %let last = %eval((&i. + 1)*&size.);
    %end;

[Reeza]

First, Excel files are not the same as CSV and export to a CSV is much more trivial.

The program linked is a macro program that you can easily adapt and only need to add the PROC EXPORT once. The macro splits the files you just need to export it to a name of your choice. Go through it, run it with some sample data - it set up to run with some data from SASHELP so should run right away for you.

Once it's working to generate the data set add your export step.

Or I answered a very similar question earlier today about exporting to CSV by group variable. It would be trivial to create a grouping variable for every 500 records and then use the same approach on your CSV files. That question has a few other solutions that you may find easier to adapt as well.

Honestly, this question is asked an answered at least monthly, if you search on here you'll find many data step solutions, some exactly what you need and some where you need to add a variation. Pick one approach and try and work through that.

[Ksharp]

Split it and export it.

data have;
set sashelp.heart;
run;











%macro split_data(dataset= , n= ,path= );
%let dsid=%sysfunc(open(&dataset));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
%let group=%sysevalf(&nobs/&n,ceil);

data %do i=1 %to &group ;  want&i  %end;  ;
 set &dataset;
 select(ceil(_n_/&n));
%do i=1 %to &group ;
 when(&i) output want&i;
%end;
 otherwise;
 end;
run;

%do i=1 %to &group ;
proc export data=want&i outfile="&path.\want&i..xlsx" dbms=xlsx replace;
run;
%end;
%mend;

%split_data(dataset=have, n=500 ,path=c:\temp\ )