creating a sequence variable based on other variables

[LOLO12]

Hi All, 

I tried to create a sequence variable based on usubjid treatment visit and epoch. The issue is I can't get this right. 

* data I have; 
data example;
length usubjid pair_sph epoch $20.;
input trtn $ usubjid $ visitnum visit $ epoch $ pair_add $ pair_sph $ ;
datalines;
TRT1 1 1 VISIT1 TRT1-ASSGN1-1 HGH/HGH -3.50/-2.25
TRT1 1 1 VISIT1 TRT1-ASSGN1-1 /HGH /-2.25
TRT1 1 2 VISIT2 TRT1-ASSGN5-1 HGH/ -3.75/
TRT1 1 2 VISIT2 TRT1-ASSGN5-1 /HGH /-2.25
TRT2 1 4 VISIT4 TRT2-ASSGN1-1 / -3.50/0.00
TRT1 2 1 VISIT1 TRT1-ASSGN1-1 HGH/HGH -2.50/-2.50
TRT1 2 2 VISIT2 TRT1-ASSGN5-1 HGH/ -2.75/
TRT2 2 4 VISIT4 TRT2-ASSGN1-1 / -2.50/-0.50
TRT2 2 5 VISIT5 TRT2-ASSGN5-1 / /-1.00
TRT1 3 1 VISIT1 TRT1-ASSGN1-1 HGH/HGH -1.50/-1.50
TRT1 3 2 VISIT2 TRT1-ASSGN5-1 HGH/ -1.25/
TRT2 3 4 VISIT4 TRT2-ASSGN1-1 / 0.50/-1.50
TRT1 4 1 VISIT1 TRT1-ASSGN1-1 / 0.50/-2.00
TRT2 4 4 VISIT4 TRT2-ASSGN1-1 HGH/HGH -1.75/-2.00
;
run;

*data I want; 

usubjid	pair_sph	epoch	trtn	visitnum	visit	pair_add	Seq
1	-3.50/-2.25	TRT1-ASSGN1-1	TRT1	1	VISIT1	HGH/HGH	1
1	/-2.25	TRT1-ASSGN1-1	TRT1	1	VISIT1	/HGH	1
1	-3.75/	TRT1-ASSGN5-1	TRT1	2	VISIT2	HGH/	2
1	/-2.25	TRT1-ASSGN5-1	TRT1	2	VISIT2	/HGH	2
1	-3.50/0.00	TRT2-ASSGN1-1	TRT2	4	VISIT4	/	1
2	-2.50/-2.50	TRT1-ASSGN1-1	TRT1	1	VISIT1	HGH/HGH	1
2	-2.75/	TRT1-ASSGN5-1	TRT1	2	VISIT2	HGH/	2
2	-2.50/-0.50	TRT2-ASSGN1-1	TRT2	4	VISIT4	/	1
2	/-1.00	TRT2-ASSGN5-1	TRT2	5	VISIT5	/	2
3	-1.50/-1.50	TRT1-ASSGN1-1	TRT1	1	VISIT1	HGH/HGH	1
3	-1.25/	TRT1-ASSGN5-1	TRT1	2	VISIT2	HGH/	2
3	0.50/-1.50	TRT2-ASSGN1-1	TRT2	4	VISIT4	/	1
4	0.50/-2.00	TRT1-ASSGN1-1	TRT1	1	VISIT1	/	1
4	-1.75/-2.00	TRT2-ASSGN1-1	TRT2	4	VISIT4	HGH/HGH	1

[ballardw]

Please describe the rules in words on how the "other variables" are used in creating the sequence.

[Tom]

So assuming by " usubjid treatment visit and epoch" you mean

  by usubjid trtn visit epoch;

And the data is sorted in that order then a simple data step with by group processing should do what you want.

data want ;
  set example;
  by usubjid trtn visit epoch;
  if first.usubjid then seq=0;
  seq + first.epoch;
run;

Result

Note the results are different for the last observation for USUBJID=1 since for some reason in your example result you but SEQ back to 1 on that observation instead of incrementing it to 3.  Is there some reason that SEQ should be 1 for that observation?

[LOLO12]

not right if trt2 and visit 4 then seq=1; 

[Tom]

---

@LOLO12 wrote:

not right if trt2 and visit 4 then seq=1; 

---

Can you please explain what that means?

Do you mean if TRTN is equal to 'TRT2' and VISIT is equal to 'VISIT4' then you want the number to restart with 1?

If so then add this statement just before the RUN statement.

if TRTN='TRT2' and VISIT='VISIT4' then seq=1;

[LOLO12]

we will count again from 0 for trt2  the sequence suppose to start over 

[ballardw]

I suggest again: provide the rules in words.

If we have to guess this may take forever as you trot out exception after exception.

Not to mention I didn't see any 0 for your sequence in your Want example. So I don't follow this "count from 0".

---

@LOLO12 wrote:

we will count again from 0 for trt2  the sequence suppose to start over 

---

[LOLO12]

I meant start over the count for the second treatment because it is a different treatment. 

your table sequence doesn't match with my table .  The second treatment has started with 1 again.  we need to start the count for the trt2. I changed the color below. Hopefully this will help. 

usubjid	pair_sph	epoch	trtn	visitnum	visit	pair_add	Seq
1	-3.50/-2.25	TRT1-ASSGN1-1	TRT1	1	VISIT1	HGH/HGH	1
1	/-2.25	TRT1-ASSGN1-1	TRT1	1	VISIT1	/HGH	1
1	-3.75/	TRT1-ASSGN5-1	TRT1	2	VISIT2	HGH/	2
1	/-2.25	TRT1-ASSGN5-1	TRT1	2	VISIT2	/HGH	2
1	-3.50/0.00	TRT2-ASSGN1-1	TRT2	4	VISIT4	/	1
2	-2.50/-2.50	TRT1-ASSGN1-1	TRT1	1	VISIT1	HGH/HGH	1
2	-2.75/	TRT1-ASSGN5-1	TRT1	2	VISIT2	HGH/	2
2	-2.50/-0.50	TRT2-ASSGN1-1	TRT2	4	VISIT4	/	1
2	/-1.00	TRT2-ASSGN5-1	TRT2	5	VISIT5	/	2
3	-1.50/-1.50	TRT1-ASSGN1-1	TRT1	1	VISIT1	HGH/HGH	1
3	-1.25/	TRT1-ASSGN5-1	TRT1	2	VISIT2	HGH/	2
3	0.50/-1.50	TRT2-ASSGN1-1	TRT2	4	VISIT4	/	1
4	0.50/-2.00	TRT1-ASSGN1-1	TRT1	1	VISIT1	/	1
4	-1.75/-2.00	TRT2-ASSGN1-1	TRT2	4	VISIT4	HGH/HGH	1

[Tom · Accepted solution]

So you want to restart the counting at FIRST.TRTN instead of FIRST.USUBJID.

data want ;
  set example;
  by usubjid trtn visit epoch;
  if first.trtn then seq=0;
  seq + first.epoch;
run;