need to count positive and negative PID's

[vraj1]

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

[PeterClemmensen]

Does this logic has to hold wihtin each PID ?

[vraj1]

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

[PeterClemmensen]

Why isn't count = 2 for treatment A in Negative? Both PID = 101 and PID = 102 has all negative values. 

[vraj1]

sorry my mistake. it is 2

	A	B	C
Positive		1	1
Negative	2		1

[PeterClemmensen]

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:

[vraj1]

Thanks, It works. I need to transpose again as proc tabulate out= option doesnt transpose the output right?

[PeterClemmensen]

Do you need the result as a data set or as a report?

[vraj1]

As dataset

[Ksharp]

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;