BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

View solution in original post

6 REPLIES 6
Reeza
Super User

You should be doing if first.subject not treatment.

jenim514
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Tourmaline | Level 20

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
Super User

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 4158 views
  • 2 likes
  • 4 in conversation