topic Re: Scanning a series of values to generate summary variable in SAS Programming

Scanning a series of values to generate summary variable

EB1 — Wed, 20 Sep 2017 16:19:48 GMT

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 Negative

2 Negative

3 Negative

3 Positive

3 Negative

And I need one that looks like this:

ID Test Outcome Overall

1 Positive Positive

1 Negative Positive

2 Negative Negative

3 Negative Positive

3 Positive Positive

3 Negative Positive

Thanks in advance!

Re: Scanning a series of values to generate summary variable

Reeza — Wed, 20 Sep 2017 16:30:08 GMT

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).

Re: Scanning a series of values to generate summary variable

stat_sas — Wed, 20 Sep 2017 16:38:08 GMT

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;