Scanning a series of values to generate summary variable

Accepted Solution Solved
Reply
Occasional Contributor EB1
Occasional Contributor
Posts: 12
Accepted Solution

Scanning a series of values to generate summary variable

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!


Accepted Solutions
Solution
‎09-20-2017 02:19 PM
Super User
Posts: 23,262

Re: Scanning a series of values to generate summary variable

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

View solution in original post


All Replies
Solution
‎09-20-2017 02:19 PM
Super User
Posts: 23,262

Re: Scanning a series of values to generate summary variable

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

Trusted Advisor
Posts: 1,270

Re: Scanning a series of values to generate summary variable

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 122 views
  • 0 likes
  • 3 in conversation