topic Re: count unique subject by tx group in SAS Programming

count unique subject by tx group

jenim514 — Thu, 16 Aug 2018 22:11:45 GMT

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!!

Re: count unique subject by tx group

Reeza — Thu, 16 Aug 2018 22:14:58 GMT

You should be doing if first.subject not treatment.

Re: count unique subject by tx group

jenim514 — Thu, 16 Aug 2018 22:22:26 GMT

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.

Re: count unique subject by tx group

Reeza — Thu, 16 Aug 2018 22:25:23 GMT

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.

Re: count unique subject by tx group

jenim514 — Thu, 16 Aug 2018 22:28:08 GMT

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

Re: count unique subject by tx group

novinosrin — Thu, 16 Aug 2018 23:05:54 GMT


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;

Re: count unique subject by tx group

ballardw — Thu, 16 Aug 2018 23:17:12 GMT

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;