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!!
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;
You should be doing if first.subject not treatment.
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.
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.
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 |
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.