Adding a 2nd sequencing/counter variable

[Shain22]

Hi,

Here is the code I have following code for N1 variable in the sample dataset below:

proc sort data=dat.TestData out= test1;
by trt;
data test2;
set test1;
N1 + 1;
by trt;
if first.trt then N1 = 1;
run;

I need to add N2 variable. It's a sequential number with same value for first 12 records within a TRT group:

ID	TRT	N1	N2
101_017200024	TRT-A	1	1
101_330700016	TRT-A	2	1
102_0235003133	TRT-B	1	2
102_0235009018	TRT-B	2	2
102_0240009014	TRT-B	3	2
102_0240009014	TRT-B	4	2
102_0301006034	TRT-B	5	2
102_0301006034	TRT-B	6	2
102_0800009074	TRT-B	7	2
102_0805003099	TRT-B	8	2
102_0808009078	TRT-B	9	2
102_1302003096	TRT-B	10	2
103_010500013	TRT-B	11	2
103_011100001	TRT-B	12	2
103_011400013	TRT-B	13	3
103_011500002	TRT-B	14	3
103_011500002	TRT-B	15	3
103_011500015	TRT-B	16	3
103_011700008	TRT-B	17	3
103_011900013	TRT-B	18	3
103_011900020	TRT-B	19	3
103_012200004	TRT-C	1	4
103_012200005	TRT-C	2	4
103_012400003	TRT-C	3	4
103_012700022	TRT-C	4	4
103_012700022	TRT-C	5	4

Thank you!

[novinosrin]

add this to code

if first.trt then N2 + 1;

A cleaner way would be

if first.trt then do;
 N1=1;
N2+1;
end;

[Shain22]

Unfortunately, that's not what I need. If you see the sample data, the N2 repeats with in the group for 1st 12 records.

[mkeintz · Accepted solution]

You need a way to make sure to change N2 when it becomes "stale", i.e. when the same value has been established for 12 records:

data test2;
  set test1;
  by trt;
  N1 + 1;
  if first.trt then N1 = 1;
  if first.trt or lag11(n2)=n2 then n2+1;
run;

Editted note:  If a treatment has, say 30 records, then I presume the first 12 records have one value for N2, then the next 12 have the next N2 value, and a 3rd N2 value is used for the last 6 records. 

[Shain22]

Thank you!

[Tom]

Just increment N2 when you start a new block of 12.  Either because you started an new TRT group or you reach n1=13,25,etc.

data want;
  set have;
  by trt;
  n1+1;
  if first.trt then n1=1;
  n2+(1=mod(n1,12));
run;

[Shain22]

Thanks...very creative solution!