Scanning a series of values to generate summary variable

[EB1]

Hi all,

I am wondering how to scan a series of values for each ID and generate a summary variable for each ID. Essentially, I need to look at all the tests each patient has had, and decide if they have ever been positive. The dataset I have is the following:

ID     Test Outcome

1         Positive

1         Positive

1         Negative

2         Negative

2         Negative

3         Negative

3         Negative

3         Positive

3         Negative

And I need one that looks like this:

ID     Test Outcome      Overall

1          Positive              Positive

1          Positive              Positive

1          Negative            Positive

2          Negative            Negative

2          Negative            Negative

2          Negative            Negative

3          Negative            Positive

3          Negative            Positive

3          Positive              Positive

3          Negative            Positive

Thanks in advance!

[Reeza · Accepted solution]

There are many ways to do this one. 

If you're brand new to SAS, easiest approach is:

1. Sort on ID and DESCENDING test outcome

2. Use RETAIN to keep the first value across the ID. If it's POSITIVE it will be the first record because (P is after N alphabetically).

[stat_sas]

Hi,

Using proc sql:

proc sql;
create table want as
select *,case when sum(Test_Outcome="Positive")>0 then "Positive" else "Negative" end as Overall
from have group by id
order by id;
quit;