topic Re: How do I select value closest to X? in SAS Data Management

How do I select value closest to X?

pamplemouse22 — Wed, 26 Oct 2016 22:21:23 GMT

Hi,

I am working with a dataset that looks like this. Each participant was scored multiple times and age at exam was recorded.

id visit age score

1 1 80 30

1 2 85 28

1 3 86 25

2 1 84 25

2 2 87 24

3 1 90 28

3 2 95 28

I want to create a new variable that reports the score (score_new) closest to age 85.

Particularly, I want the score at age 85. If there is no value for this, then I want the score at an age +/- 2 years, using the score closest to age 85. If there is no value for this either, then I want the score recorded after age 85, again using the score closest to 85. If there is a tie (e.g., a score at 84 and 86), then I want to keep the younger score. Remaining can be missing.

So, I would like to end with a final data set like this:

id visit age score score_new

1 1 80 30 .

1 2 85 28 28

1 3 86 25 .

2 1 84 25 25

2 2 86 24 .

3 1 90 28 28

3 2 95 28 .

Any help would be appreciated. Happy to explain further. I am new to arrays, so replies with detail would be best.

Looking forward to your feedback and solving this!

Thanks.

Re: How do I select value closest to X?

PGStats — Thu, 27 Oct 2016 02:58:27 GMT

Here is a simple way:

data closenessRanks;
input age rank;
datalines;
85 0
84 1
86 2
83 3
87 4
88 5
89 6
90 7
91 8
92 9
93 10
94 11
95 12
96 13
97 14
98 15
99 16
;

data have;
input id visit age score;
datalines;
1 1 80 30      
1 2 85 28
1 3 86 25
2 1 84 25 
2 2 87 24
3 1 90 28
3 2 95 28
;

proc sql;
create table want as
select a.*,
    case when rank=min(rank) then score else . end as newScore
from 
    have as a left join 
    closenessRanks as b on a.age=b.age
group by id
order by id, visit;
select * from want;
quit;

Re: How do I select value closest to X?

Ksharp — Thu, 27 Oct 2016 03:10:07 GMT

Maybe I understood what you are talking about.

data have;
input id visit age score;
cards;
1 1 80 30      
1 2 85 28
1 3 86 25
2 1 84 25 
2 2 87 24
3 1 90 28
3 2 95 28
;
run;
proc sql;
create table temp1 as
 select *
  from have
   group by id
    having sum(age=85)=0 and age in (83 84 86 87)
     order by id,age;
create table temp2 as
 select *
  from have
   group by id
    having sum(age in (83 84 85 86 87))=0 and age gt 87
     order by id,age;
quit;
data key1;
 set temp1;
 by id;
 if first.id;
run;
data key2;
 set temp2;
 by id;
 if first.id;
run;
data key;
 set have(where=(age=85)) key1 key2;
 keep id age score;
 rename score=new_score;
run;

data want;
 merge have key;
 by id age;
run;

Re: How do I select value closest to X?

pamplemouse22 — Thu, 27 Oct 2016 04:22:03 GMT

Thank you so much! This works, but there's one small issue to fix. For individuals where none of the scores match one of the ranked ages, the original score is being used as newscore. Instead, I would like these to be missing. How can I change this? Thank you.

Re: How do I select value closest to X?

pamplemouse22 — Thu, 27 Oct 2016 04:12:11 GMT

Thank you very much!

Re: How do I select value closest to X?

PGStats — Thu, 27 Oct 2016 04:35:22 GMT

Ok. Try

proc sql;
create table want as
select a.*,
    case when coalesce(rank, -1)=min(rank) then score else . end as newScore
from 
    have as a left join 
    closenessRanks as b on a.age=b.age
group by id
order by id, visit;
select * from want;
quit;

That should fix it.

Re: How do I select value closest to X?

pamplemouse22 — Thu, 27 Oct 2016 04:39:50 GMT

Yes, it does! Thank you.

Re: How do I select value closest to X?

pamplemouse22 — Mon, 21 Nov 2016 19:27:34 GMT

Hi PG,

I found one case when this doesn't work -- when the first visit is missing, and there are values for subsequent visits. Code ends up not pulling any value for new score, when it should. Can't figure out how to change this. Would appreciate a solution for this. Thank you in advance!

Re: How do I select value closest to X?

PGStats — Mon, 21 Nov 2016 20:20:05 GMT

The problem could be with the way you read your data. Compare the results with and without the infile statement in

data closenessRanks;
input age rank;
datalines;
85 0
84 1
86 2
83 3
87 4
88 5
89 6
90 7
91 8
92 9
93 10
94 11
95 12
96 13
97 14
98 15
99 16
;

data have;
infile datalines missover;
input id visit age score;
datalines;
1 1 80 30      
1 2 85 28
1 3 86 25
2 1 84 25 
2 2 87 24
3 1 90 28
3 2 95 28
4 1 79 88
4 2 80 99
5 1 80 
5 2 85 28
5 3 86 25
;

proc sql;
create table want as
select a.*,
    case when coalesce(rank, -1)=min(rank) then score else . end as newScore
from 
    have as a left join 
    closenessRanks as b on a.age=b.age
group by id
order by id, visit;
select * from want;
quit;