count unique subject by tx group

[jenim514]

Hi!  I feel like this one should be easy, but I cant get the count right.

I have data that looks like this when sorted (subject, day, tx).  I am trying to get the 'WANT' column count to look like this:

WANT	SUBJECT	DAY	TX
1	1234	0	A
1	1234	7	A
1	1234	14	A
1	1234	21	A
2	4321	0	G
2	4321	7	G
2	4321	14	G
2	4321	21	G

I tried this:

proc sort data=cfrsd out=tot_data; by usubjid tx avisitn; run;
data cfrsd1;
set tot_data;
by usubjid tx;
if first.tx then count=0;
count +1;
run;

but the 'WANT' column is producing 1234, 1234 instead of 1111,2222

Any help is appreciated!!

[Reeza]

You should be doing if first.subject not treatment.

[jenim514]

I think i need to do by tx group somehow  because same subject can appear in two groups (the tx group and the total group- which has it's own group letter).  If I count by subject, it just counts 12345678 for the two groups.  I need 1111,2222.   

[Reeza]

Can you illustrate that with your example data?

---

@jenim514 wrote:

I think i need to do by tx group somehow  because same subject can appear in two groups (the tx group and the total group- which has it's own group letter).  If I count by subject, it just counts 12345678 for the two groups.  I need 1111,2222.   

---

[jenim514]

here is some example data with the subjects appearing in two tx groups.

WANT	SUBJECT	DAY	TX
1	1234	0	A
1	1234	7	A
1	1234	14	A
1	1234	21	A
2	4321	0	G
2	4321	7	G
2	4321	14	G
2	4321	21	G
3	1234	0	T
3	1234	7	T
3	1234	14	T
3	1234	21	T
4	4321	0	T
4	4321	7	T
4	4321	14	T
4	4321	21	T

[novinosrin · Accepted solution]

data have;
input WANT	SUBJECT	DAY	TX $;
drop want;
cards;
1	1234	0	A
1	1234	7	A
1	1234	14	A
1	1234	21	A
2	4321	0	G
2	4321	7	G
2	4321	14	G
2	4321	21	G
3	1234	0	T
3	1234	7	T
3	1234	14	T
3	1234	21	T
4	4321	0	T
4	4321	7	T
4	4321	14	T
4	4321	21	T
;

proc sort data=have out=_have; by SUBJECT tx day; run;
data want;
set _have;
by SUBJECT tx;
if first.tx then count +1;
run;

or for your sample:

data want1;
set have;
by SUBJECT tx notsorted;
if first.tx then count +1;
run;

[ballardw]

I think this comes closer for the requested output.

proc sort data=have out=_have; 
   by tx SUBJECT  day; 
run;

data want;
   set _have;
   by  tx SUBJECT;
   if first.SUBJECT then count +1;
run;