BookmarkSubscribeRSS Feed
vraj1
Quartz | Level 8

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

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

Does this logic has to hold wihtin each PID ?

vraj1
Quartz | Level 8

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

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

vraj1
Quartz | Level 8

sorry my mistake. it is 2

 

  A B C
Positive   1 1
Negative 2   1
PeterClemmensen
Tourmaline | Level 20

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:

 

PeterClemmensen_0-1660046870958.png

 

vraj1
Quartz | Level 8

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

PeterClemmensen
Tourmaline | Level 20

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

vraj1
Quartz | Level 8

As dataset

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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
  • 9 replies
  • 2171 views
  • 0 likes
  • 3 in conversation