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
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;
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\ )
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.