topic Re: creating a sequence variable based on other variables in SAS Programming

creating a sequence variable based on other variables

LOLO12 — Tue, 02 Apr 2024 15:24:52 GMT

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

Re: creating a sequence variable based on other variables

ballardw — Tue, 02 Apr 2024 16:11:23 GMT

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

Re: creating a sequence variable based on other variables

Tom — Tue, 02 Apr 2024 16:31:06 GMT

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?

Re: creating a sequence variable based on other variables

LOLO12 — Tue, 02 Apr 2024 16:36:20 GMT

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

Re: creating a sequence variable based on other variables

Tom — Tue, 02 Apr 2024 16:40:08 GMT

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

Re: creating a sequence variable based on other variables

LOLO12 — Tue, 02 Apr 2024 16:45:19 GMT

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

Re: creating a sequence variable based on other variables

ballardw — Tue, 02 Apr 2024 17:00:39 GMT

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

Re: creating a sequence variable based on other variables

LOLO12 — Tue, 02 Apr 2024 17:08:11 GMT

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

Re: creating a sequence variable based on other variables

Tom — Tue, 02 Apr 2024 18:11:06 GMT

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;