I need to count positive and negative PID's based on trt like below.
A | B | C | |
Positive | 1 | 1 | |
Negative | 1 | 1 |
Positive is when PID is negative at BL="Y" and then changes to positive later at anytime. and negative means PID is negative everywhere.
Can any one help me how to do this.
Sample data
DATA WORK.ADA_0001;
LENGTH
PID 8
result $ 8
trt $ 1
Visit 8
BL $ 1 ;
FORMAT
PID BEST12.
result $CHAR8.
trt $CHAR1.
Visit BEST12.
BL $CHAR1. ;
INFORMAT
PID BEST12.
result $CHAR8.
trt $CHAR1.
Visit BEST12.
BL $CHAR1. ;
INFILE DATALINES4
DLM='7F'x
MISSOVER
DSD ;
INPUT
PID : BEST32.
result : $CHAR8.
trt : $CHAR1.
Visit : BEST32.
BL : $CHAR1. ;
DATALINES4;
101NEGATIVEA1Y
102NEGATIVEA1Y
102NEGATIVEA2
103NEGATIVEB1Y
103NEGATIVEB2
103POSITIVEB3
103POSITIVEB4
104NEGATIVEC1Y
104NEGATIVEC2
104NEGATIVEC3
105NEGATIVEC1Y
105NEGATIVEC2
105POSITIVEC3
;;;;
Does this logic has to hold wihtin each PID ?
Within each PID if BL="Y" is negative and within PID if there is any positve then that PID will be positive and if all are negative then that PID comes under negative. Then i need to count number of positives and negatives under trt
Why isn't count = 2 for treatment A in Negative? Both PID = 101 and PID = 102 has all negative values.
sorry my mistake. it is 2
A | B | C | |
Positive | 1 | 1 | |
Negative | 2 | 1 |
Try this
data ADA_0001;
input PID result $ trt $ Visit BL $;
infile datalines missover;
datalines;
101 NEGATIVE A 1 Y
102 NEGATIVE A 1 Y
102 NEGATIVE A 2
103 NEGATIVE B 1 Y
103 NEGATIVE B 2
103 POSITIVE B 3
103 POSITIVE B 4
104 NEGATIVE C 1 Y
104 NEGATIVE C 2
104 NEGATIVE C 3
105 NEGATIVE C 1 Y
105 NEGATIVE C 2
105 POSITIVE C 3
;
data temp;
dp = 0;
dn = 0;
do until (last.PID);
set ADA_0001;
by PID;
if result = 'NEGATIVE' and BL = 'Y' then dp = 1;
if result = 'POSITIVE' then dn = 1;
if result = 'POSITIVE' and dp = 1 then do;
cp = 1;
dp = 0;
end;
else cp = .;
if last.PID and dn = 0 then cn = 1;
output;
end;
run;
Proc tabulate data = temp;
class trt result;
Var cn cp;
tables (cn='Negative' cp='Positive')*sum=''*f=8.
, trt='';
Run;
Result:
Thanks, It works. I need to transpose again as proc tabulate out= option doesnt transpose the output right?
Do you need the result as a data set or as a report?
As dataset
data ADA_0001;
input PID result $ trt $ Visit BL $;
infile datalines missover;
datalines;
101 NEGATIVE A 1 Y
102 NEGATIVE A 1 Y
102 NEGATIVE A 2
103 NEGATIVE B 1 Y
103 NEGATIVE B 2
103 POSITIVE B 3
103 POSITIVE B 4
104 NEGATIVE C 1 Y
104 NEGATIVE C 2
104 NEGATIVE C 3
105 NEGATIVE C 1 Y
105 NEGATIVE C 2
105 POSITIVE C 3
;
data temp;
do i=1 by 1 until(last.trt);
set ADA_0001;
by PID trt;
if result='POSITIVE' then has_p=1;
if result='NEGATIVE' and BL='Y' then do;flag=1;_i=i;end;
end;
if not has_p then n_p='NEGATIVE';
do j=1 by 1 until(last.trt);
set ADA_0001;
by PID trt;
if flag and j>_i and result='POSITIVE' then n_p='POSITIVE';
end;
keep n_p trt;
run;
proc freq data=temp noprint;
table n_p*trt/out=temp1 ;
run;
proc transpose data=temp1 out=want(drop=_:);
by n_p;
id trt;
var count;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.