r********e
发帖数: 1686 | 1 RT
if I have a dataset as below:
id week value1
a 1 12
a 2 22
a 3 33
a 4 .
a 5 .
a 6 .
a 7 .
b 1 78
b 2 .
b 3 .
b 4 .
b 5 .
b 6 .
b 7 .
b 8 .
c 1 88
c 2 67
c 3 76
c 4 .
c 5 .
c 6 .
I need to capture the last missing value1 for each ID as value2 to achieve
the following dataset:
id week value1 value2
a 1 12 33
a 2 22 33
a 3 33 33
a 4 . 33
a 5 . 33
a 6 . 33
a 7 . 33
b 1 78 78
b 2 . 78
b 3 . 78
b 4 . 78
b 5 . 78
b 6 . 78
b 7 . 78
b 8 . 78
c 1 88 76
c 2 67 76
c 3 76 76
c 4 . 76
c 5 . 76
c 6 . 76
How can I do this? Thanks a lot! |
r********e
发帖数: 1686 | |
l*********s
发帖数: 5409 | 3 1)create a dataset holding the last nonmissing values for each group
2)do a sql join with original data set |
k*******a
发帖数: 772 | 4 写了一个，不过有点繁
data a;
input id $ week value1;
datalines;
a 1 12
a 2 22
a 3 33
a 4 .
a 5 .
a 6 .
a 7 .
b 1 78
b 2 .
b 3 .
b 4 .
b 5 .
b 6 .
b 7 .
b 8 .
c 1 88
c 2 67
c 3 76
c 4 .
c 5 .
c 6 .
;
data b;
set a;
by id;
if value1 ne . then do
value2=value1;
retain value2;
end;
if last.id then output;
keep id value2;
data a;
merge a b;
by id;
proc print data=a;run; |
R*********i
发帖数: 7643 | 5 It souds like a typical LOCF question:
proc sort data=a;
by id week;
data locf;
set a;
by id week;
where value1>.;
if last.id;
data out;
merge a locf (keep=id value1 rename=(value1=value2));
by id ;
proc print;
run; |
l**********9
发帖数: 148 | 6
Nice~~~
set by id就可以了吧？不需要by week了吧？
【在 R*********i 的大作中提到】
: It souds like a typical LOCF question:
: proc sort data=a;
: by id week;
: data locf;
: set a;
: by id week;
: where value1>.;
: if last.id;
: data out;
: merge a locf (keep=id value1 rename=(value1=value2));
|
