topic Re: Assign serial number to observations in SAS Programming

Assign serial number to observations

dapenDaniel — Mon, 03 Dec 2018 02:47:30 GMT

Hello! I have a data set with more than 500k observations. I would like to assign serial numbers (1-5000) to observations and then split the file at each 5000 point so that it can be splited into 100 files. What code do I need? Thanks.

Split dataset

dapenDaniel — Sun, 02 Dec 2018 05:27:46 GMT

Hello. I have a data set with more than 10000 observations. I would like to split it into several data sets with 50 observations for each. I know I can use the group = mod(_n_, 250) function to create a variable and later use if then output function to finish this task but It means that I need to create 250 groups. Is there any more convenient way to split this data set? Thanks.

Re: Split dataset

novinosrin — Sun, 02 Dec 2018 05:35:20 GMT

Why split? Keep it and do by group processing. Dataset with 100000 obs is peanuts

Also I like your mod function approach if you go with that. I trust for such a tiny dataset, yours is robust

If you are working on a well designed dimensional model star schema that is denormalized to 2nd NF dims, splitting can only cause more problems and join after join later. Rather embrace, the denormalized set for extensive OLAP slice and dice

Re: Split dataset

Reeza — Sun, 02 Dec 2018 06:20:34 GMT

You create one variable with N different values.

Re: Split dataset

Reeza — Sun, 02 Dec 2018 06:22:34 GMT

And someone asked this earlier THIS week and Tom posted a single data step solution that was neat. That being said, this is usually a bad idea. Then you end up in a circle of macro loops usually. The only good reason to do this is when you have to deliver datasets as csv, and even then there are better solutions than splitting. A dynamic filter within proc export for example.

Re: Split dataset

dapenDaniel — Sun, 02 Dec 2018 06:34:38 GMT

Thanks. The size of my data set is too big. Currently I am using a supercomputer from my university to run this data set. If the size is too large, it will take longer time to run the whole process. Can you copy the link that Tom answered to me? Thanks.

Re: Split dataset

Reeza — Sun, 02 Dec 2018 07:13:10 GMT

10000 obs is not big. That’s a small data set, not small enough for small data but not huge by any means at all. General rule, if it fits in Excel it isn’t big.

Re: Split dataset

Reeza — Sun, 02 Dec 2018 07:24:18 GMT

https://communities.sas.com/t5/SAS-Programming/export-split-datasets-as-txt-files/m-p/516861/highlight/true#M139634

This is for text files.

Re: Assign serial number to observations

Astounding — Mon, 03 Dec 2018 03:18:03 GMT

Assigning a serial number is easy:

data want;

set have;

if mod(_n_, 5000) = 1 then serial_number + 1;

run;

But the bigger question is why you would want to split the file. In the vast majority of the cases, you can accomplish what you need by using a BY statement in later steps:

by serial_number;

So a little detail there might be helpful.

Re: Assign serial number to observations

Allaluiah — Mon, 03 Dec 2018 03:20:42 GMT

You are unnecessarily duplicating

https://communities.sas.com/t5/New-SAS-User/Split-dataset/m-p/517827#M3432

wonder why do you have to do that over and over

Re: Assign serial number to observations

dapenDaniel — Mon, 03 Dec 2018 03:25:46 GMT

The size of the original data set is more than 18 GB and I need to use a supercomputer to run the data. I can split this large dataset into several files with small size so that I can submit more orders to run the data faster.

Re: Assign serial number to observations

Reeza — Mon, 03 Dec 2018 04:16:32 GMT

I've merged these threads, since they are the same question.

Here is the solution.
https://communities.sas.com/t5/SAS-Communities-Library/Splitting-a-SAS-data-set/ta-p/517960

Re: Assign serial number to observations

dapenDaniel — Mon, 03 Dec 2018 04:19:20 GMT

Thank you so much!

Re: Assign serial number to observations

ChrisNZ — Mon, 03 Dec 2018 04:42:13 GMT

Why create new tables?

Or even a new variable?

Why not process using firstobs= and osb= ?

proc ... data=XX(firstobs=1 obs=5000);

...

proc ... data=XX(firstobs=5001 obs=10000);

...

etc

This can easily be managed in a macro.

Re: Assign serial number to observations

dapenDaniel — Sun, 09 Dec 2018 19:59:54 GMT

Hello Reeza! I run the data with program and it worked well. However, the log is

NOTE: There were 1 observations read from the data set ALL2.TB3.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.00 seconds

NOTE: There were 25000 observations read from the data set ALL2.TB3.

NOTE: The data set WORK.SPLIT1 has 25000 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 9.38 seconds

cpu time 0.59 seconds

NOTE: There were 25000 observations read from the data set ALL2.TB3.

NOTE: The data set WORK.SPLIT2 has 25000 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 14.10 seconds

cpu time 0.95 seconds

There are actually 50000 observations in TB3.

I know it is not an error message but am wondering whether it is normal to have 1 observations read from the data set.

Re: Assign serial number to observations

Reeza — Sun, 09 Dec 2018 20:41:37 GMT

That's likely from the data _null_ step, note no output data set is created. I have a STOP in that data step so it ends the process after the first line. I only need the number of observations and such so reading just one line from it, which gives me all that information is quicker.

@dapenDaniel wrote:

Hello Reeza! I run the data with program and it worked well. However, the log is

NOTE: There were 1 observations read from the data set ALL2.TB3.

NOTE: DATA statement used (Total process time):

      real time           0.04 seconds

      cpu time            0.00 seconds

NOTE: There were 25000 observations read from the data set ALL2.TB3.

NOTE: The data set WORK.SPLIT1 has 25000 observations and 4 variables.

NOTE: DATA statement used (Total process time):

      real time           9.38 seconds

      cpu time            0.59 seconds

NOTE: There were 25000 observations read from the data set ALL2.TB3.

NOTE: The data set WORK.SPLIT2 has 25000 observations and 4 variables.

NOTE: DATA statement used (Total process time):

      real time           14.10 seconds

      cpu time            0.95 seconds

There are actually 50000 observations in TB3.

I know it is not an error message but am wondering whether it is normal to have 1 observations read from the data set.

Re: Assign serial number to observations

dapenDaniel — Sun, 09 Dec 2018 21:26:52 GMT

Thanks.