topic Re: Splitting a dataset in SAS Programming

Splitting a dataset

adammom — Thu, 17 Nov 2016 02:55:28 GMT

Hello, I found a real good macro here once that split a huge dataset (500K) records into equal portions of 30,000 and it was so simple, but now I can't find it. I would like to split a file only 34K records by account number into equal records of 500 records. I know I know its about 64 datasets, however that is all the other end can handle.

thank you.

I don't have anything to attach as I am looking for the example I had previously.

Re: Splitting a dataset

Reeza — Thu, 17 Nov 2016 05:03:12 GMT

Splitting files is relatively trivial if you have clear rules.

You say account number and 500 records? Does that mean each file is for a single account and maximum 500 records? Or can a file of 500 records have multiple accounts?

What is the final output format, Excel, csv, sas7bdat data set?

Split it by grouping variable:

http://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

By amount

https://communities.sas.com/t5/Base-SAS-Programming/split-file-into-multiple-files/td-p/56457

If your final output is a text file use file options, see example 4&5 here

https://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n15o12lpyoe4gfn1y1vcp6xs6966.htm

Re: Splitting a dataset

RW9 — Thu, 17 Nov 2016 11:07:50 GMT

To be honest, unless there is a clear reason (maybe file transmittal can only old a certain amount) then its not a good idea to split data. It just means you end up with lots of segments to program on.

Its is trvial however to do:

data _null_;
  set your_data nobs=n;
  do i=1 to (n/500);
    call execute('data temp;  set your_data point='||put(i-1*500)||' obs=500; run;');
    call execute('proc export data=temp outfile="<path to file>\File'||strip(put(i,best.))||'.xls"; run;');
  end;
run;

Do note, not tested, but something like that should work.

Re: Splitting a dataset

adammom — Thu, 17 Nov 2016 13:06:50 GMT

Thank you! Will give it a try. I sent a file of 32k and was told I had to split it in increments of 500.

Re: Splitting a dataset

RW9 — Thu, 17 Nov 2016 13:24:13 GMT

If your doing a data transfer then best to use an appropriate format, CSV, XML, etc.