How to identify correct treatment

[r3570]

Hi All,

Could anyone help me with the below requirement?

I have 3 variables SUBJ CODE TRT  which have values as following.

SUBJ     CODE   TRT

1001        AB          A

1001        AB          B

1002       CD          C

1002       CD          C

1003       EF           E

1003       EF           F

In the above data for each SUBJ there is a specific code which have corresponding TRT vales as A and B. Example, for SUBJ 1001, code is AB and TRT is having 2 records for same SUBJ and CODE with A in 1 row and B in next row. If it is 1002 then the 2 TRT records should be C and D, but if it C and C then it should be flagged. See below expectation.

SUBJ     CODE   TRT  FLAG

1001        AB          A       0

1001        AB          B        0

1002       CD          C        0

1002       CD          C         1

1003       EF           E        0

1003       EF           F         0

1004       AD          A        0

1004       AD           A        1

Can anyone help me achieve this?

[Kurt_Bremser · Accepted solution]

data have;
input SUBJ $ CODE $ TRT $;
datalines;
1001 AB A
1001 AB B
1002 CD C
1002 CD C
1003 EF E
1003 EF F
1004 AD A
1004 AD A
;

data want;
set have;
by subj code trt notsorted;
if
  first.trt ne last.trt /* indicates consecutive observations with same value */
  and last.trt /* we want to flag only the second occurence */
then flag = 1;
else flag = 0;
run;

proc print data=want noobs;
run;

Result:

SUBJ	CODE	TRT	flag
1001	AB	A	0
1001	AB	B	0
1002	CD	C	0
1002	CD	C	1
1003	EF	E	0
1003	EF	F	0
1004	AD	A	0
1004	AD	A	1

[r3570]

@Kurt_Bremser 

Thanks for your program. I would like to make this program as dynamic like if the CODE variable has ABC instead of AB and TRT should have 3 records with A, B, C then how can we modify the program?

[Kurt_Bremser]

The BY statement does not care about the length of variables. As long as there are two (or more) identical TRT values following each other within a SUBJ/CODE group, the flag will be set.

If there's more to it, you have to supply additional example data (see how it's done in a DATA step), and the expected result from this.

[r3570]

Lets say my data is like this

SUBJ     CODE   TRT

1001        ABC         A

1001        ABC          B

1001        ABC          C

1002       CDE          C

1002       CDE          C

1002       CDE          E

1003       EFG          E

1003       EFG          F

1003       EFG          F

I NEED output like this

SUBJ     CODE   TRT      FLAG

1001        ABC         A           0

1001        ABC          B          0

1001        ABC          C          0

1002       CDE          C          0

1002       CDE          C          1

1002       CDE          E          0

1003       EFG          E          0

1003       EFG          F          0

1003       EFG          F           1

[Kurt_Bremser]

The same code works:

data have;
input SUBJ $ CODE $ TRT $;
datalines;
1001 ABC A
1001 ABC B
1001 ABC C
1002 CDE C
1002 CDE C
1002 CDE E
1003 EFG E
1003 EFG F
1003 EFG F
;

data want;
set have;
by subj code trt notsorted;
if
  first.trt ne last.trt /* indicates consecutive observations with same value */
  and last.trt /* we want to flag only the second occurence */
then flag = 1;
else flag = 0;
run;

proc print data=want noobs;
run;

Result:

SUBJ	CODE	TRT	flag
1001	ABC	A	0
1001	ABC	B	0
1001	ABC	C	0
1002	CDE	C	0
1002	CDE	C	1
1002	CDE	E	0
1003	EFG	E	0
1003	EFG	F	0
1003	EFG	F	1

[Ksharp]

data have;
input SUBJ $ CODE $ TRT $;
datalines;
1001 ABC A
1001 ABC B
1001 ABC C
1002 CDE C
1002 CDE C
1002 CDE E
1003 EFG E
1003 EFG F
1003 EFG F
;
data want;
 do until(last.subj);
  set have;
  by subj;
  length temp $ 80;
  temp=cats(temp,trt);
 end;
 do i=1 by 1 until(last.subj);
  set have;
  by subj;
  flag=ifn(char(code,i)=char(temp,i),0,1);
  output;
 end;
drop temp i;
run;