topic Re: Group and export data in SAS Programming

Group and export data

GS2 — Thu, 29 Jul 2021 20:16:38 GMT

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

Re: Group and export data

Reeza — Thu, 29 Jul 2021 20:29:45 GMT

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

Re: Group and export data

GS2 — Thu, 29 Jul 2021 21:08:45 GMT

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

Re: Group and export data

jimbarbour — Thu, 29 Jul 2021 21:12:30 GMT

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;

Re: Group and export data

Reeza — Thu, 29 Jul 2021 21:22:10 GMT

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.

Re: Group and export data

Ksharp — Fri, 30 Jul 2021 13:42:13 GMT

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\ )