J*y
发帖数: 271 | 1 I have a dataset looks as the following:
Name Score
A 766
A 9
A 93
A 869
A 143
B 8
B 119
B 362
B 6
B 95
C 524
C 99
C 123
C 176
C 210
...
I need to do the following:
1. if the score value less than 10, set the score to missing
2. if there is only one score less than 10 for each name group, then set the
second minimum score to missing. (that's where I am stuck. )
for example, the above table:
for Name A, 9 and 93 should set to missing;
for Name B, 8 and 6 should set to missing ...
BTW, the number of each name is always 5. | s*********e
发帖数: 1051 | 2 这行吗？
data one;
input Name $ Score;
if score < 10 then score = .;
datalines;
A 766
A 9
A 93
A 869
A 143
B 8
B 119
B 362
B 6
B 95
C 524
C 99
C 123
C 176
C 210
;
run;
proc sort data = one;
by name score;
run;
data two;
set one;
by name score;
if first.name then n = 1; else n + 1;
if n = 2 and lag(score) = . then score = .;
run;
proc print data = _last_; run; | m***c
发帖数: 118 | 3 proc sql;
create table final(drop=n score2) as
select *,ifn(n=1and score2=min(score2),.,score2) as score3
from (select *,ifn(score<10,.,score) as score2,sum(score<10) as n from
one group by name)
group by name
order by name;
quit; | J*y
发帖数: 271 | 4 Thank you!
I changed missing value to 999999, then sort and get the first, and then
reset to missing. Obviously yours are better than mine by using lag
function.
【在 s*********e 的大作中提到】
: 这行吗？
: data one;
: input Name $ Score;
: if score < 10 then score = .;
: datalines;
: A 766
: A 9
: A 93
: A 869
: A 143
| J*y
发帖数: 271 | 5 It works. Thanks.
【在 m***c 的大作中提到】
: proc sql;
: create table final(drop=n score2) as
: select *,ifn(n=1and score2=min(score2),.,score2) as score3
: from (select *,ifn(score<10,.,score) as score2,sum(score<10) as n from
: one group by name)
: group by name
: order by name;
: quit;
|
|
