02-04-2015 07:42 PM
I have a SAS dataset with over 200K rows, which I want to export into multiple CSV files at 100K rows each using a SAS macro. So far, I had been using Proc Export to do the same and used to keep increasing the number of exports depending upon the data volume with FIRSTOBS and OBS options.
Can somebody please help me with a sample macro code to get this done.
The CSVs should be generated based upon number of rows divided by 100K so that these get imported directly in an excel sheet using a VBA macro which I already have in place. Any help would be appreciable.
02-05-2015 04:14 AM
%let n=10 ; data class; set sashelp.class; if mod(_n_,&n)=1 then n+1; run; proc sql; create table levels as select distinct n from class; quit; data _null_; set levels; call execute(catt('proc export outfile="c:\temp\data',n,'.csv" data=class(where=(n=',n,')) dbms=csv replace;run;')); run;
02-05-2015 04:41 AM
I would however question your logic of splitting the data out to CSV then reading each one into Excel. Would not a better way round be -> eport all data from SAS to CSV (which has no limitations), then in your VBA macro read in blocks of data at a time and paste it out to new sheets. I.e.
do while readnextline is not null
output to currentsheet
if I > 100000 then new sheet
02-05-2015 07:26 AM
well actually you are correct, will need to check if/how it works...really appreciate it! The problem I forsee with this option is the sheets created after the first one might not have the headers.
02-05-2015 07:10 AM
Excel 2010 and 2013 have a maximum row limit of 1048576.
You can write directly to an Excel-format file if you have the PC files product. Just use a libname statement to create a file and then write to it with the data step or proc SQL. Each dataset/table is a separate sheet in the new file.