topic Re: Split dataset in SAS Programming

Split dataset

dapenDaniel — Wed, 14 Nov 2018 22:26:29 GMT

Hello. My data-set has more than 1 million observations and I would like to export it as excel file. However, the number of observation has beyond the maximum rows for excel. I think I should split the large dataset into 2 parts. What code do I need to use? Thanks.

Re: Split dataset

novinosrin — Wed, 14 Nov 2018 22:27:23 GMT

why not export as CSV file?

Re: Split dataset

dapenDaniel — Wed, 14 Nov 2018 22:59:26 GMT

I need to open that file in excel. Even though I export as csv file, it still cannot be shown completely if I open it in excel. Is it correct?

Re: Split dataset

ballardw — Wed, 14 Nov 2018 23:09:05 GMT

@dapenDaniel wrote:

I need to open that file in excel. Even though I export as csv file, it still cannot be shown completely if I open it in excel. Is it correct?

Why do you need to open it in Excel?

Is someone going to scroll through 1 million plus lines of data?

If the purpose is to transfer data to another program/system how does the receiving program know that it needs to pull data from 2 or more sheets?

Re: Split dataset

Reeza — Wed, 14 Nov 2018 23:59:58 GMT

Use the mod() function to create a variable that can be used to split your data set. Say you want three.

Data want1 want2 want3:

set have;

group = mod(_n_, 3);

if group = 0 then output want1;

else if group =1 then output want2;

else if group =2 then output want3;

run;

Re: Split dataset

FreelanceReinh — Fri, 16 Nov 2018 15:02:32 GMT

Hello @dapenDaniel,

With two modifications to Reeza's code you can achieve a partition into three blocks of consecutive observations of dataset HAVE (upper third, middle third and lower third):

set have nobs=n;
group = int(3*(_n_-1)/n);

Re: Split dataset

dapenDaniel — Fri, 16 Nov 2018 15:06:52 GMT

Thank you for your help!