topic Re: Create datasets in batches of 10 in SAS Programming

Create datasets in batches of 10

lsehlola — Mon, 03 Aug 2020 18:58:31 GMT

Hi All

I have a dataset with many records. I want to assign the same batch number to the first 5 records and add 1 to the batch number for the next 5 records and so on. But if the ID after the 5th record is the same as the 5th record's ID then put the 6th record in the same batch as the 5th record. In this example: 1 AA , 2 BB, 3CC, 4 EE and 4AE should be in the same batch 001. However, the 6th record has the same ID as the last record of the first batch hence it also falls into 001.

Please see below example:

ID Name Batch

1 AA 001

2 BB 001

3 CC 001

4 EE 001

4 AE 001

4 BE 001

5 FF 002

7 GG 002

8 HH 002

9 II 002

22 JJ 003

23 KK 003

24 MM 003

Any help will really be appreciated.

Re: Create datasets in batches of 10

PaigeMiller — Mon, 03 Aug 2020 19:04:54 GMT

What if the above rules require 7 with the same batch?

If we put 6 in the first batch, do we put 4 in the second batch?

Re: Create datasets in batches of 10

lsehlola — Mon, 03 Aug 2020 19:28:41 GMT

What if the above rules require 7 with the same batch?

Then ID - 5 will be in the same batch 001. Meaning you take 8 records instead of 7.

If we put 6 in the first batch, do we put 4 in the second batch?
You put 5 in the next batch unless the 6th ID is the same as the 5th ID

Re: Create datasets in batches of 10

PaigeMiller — Mon, 03 Aug 2020 20:09:12 GMT

data want;
    set have;
	retain batch 1;
	prev_id=lag(id);
	number_in_id+1;
	if number_in_id>=5 and id^=prev_id then do;
	    batch+1;
		number_in_id=0;
	end;
	drop prev_id;
run;

Re: Create datasets in batches of 10

RichardDeVen — Tue, 04 Aug 2020 02:29:36 GMT

A DOW loop whose embedded SET has by-groups processed using the NOTSORTED option of the BY statement will respond to the end of a contiguous block of values.

Example:

data want; retain batch_num 1; /* batch_num # is applied to 5 rows at a time, * extended for each row of final id in the batch */ do _n_ = 1 by 1 until (_n_ >= 5 and last.id); set have; by id NOTSORTED; OUTPUT; end; batch_num + 1; run;

Re: Create datasets in batches of 10

RichardDeVen — Mon, 03 Aug 2020 23:06:08 GMT

Why does the title say "batches of 10" and the question discusses "5 records"

Re: Create datasets in batches of 10

mkeintz — Tue, 04 Aug 2020 01:38:11 GMT

@RichardDeVen

I think you want

... "until (_n_ >= 5 and last.id)"

Re: Create datasets in batches of 10

RichardDeVen — Tue, 04 Aug 2020 02:26:01 GMT

Indeed!

Re: Create datasets in batches of 10

Ksharp — Tue, 04 Aug 2020 12:25:40 GMT

data have;
input ID Name $;
cards;
1  AA
2  BB
3  CC
4  EE
4  AE
4  BE
5  FF
5  FF
7  GG 
8  HH 
9  II
22 JJ
23 KK
24 MM
;

data want;
 set have;
 retain batch 1;
 n+1;
 if n>5 and id ne lag(id) then do;batch+1;n=1;end;
 drop n;
run;

BTW, I like this kind of Q .