Split dataset

[dapenDaniel]

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.

[novinosrin]

why not export as CSV file?

[dapenDaniel]

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?

[ballardw]

---

@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?

[Reeza · Accepted solution]

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; 

[FreelanceReinh]

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

[dapenDaniel]

Thank you for your help!