sas table将缺失值计入百分比,SAS中使用滞后的向前-向后方法填充缺失值

Assume that you have a table with user name, counter and score for each counter.

data have;

input user $ counter score;

cards;

A 1 .

A 2 .

A 3 40

A 4 .

A 5 20

A 6 .

B 1 30

B 2 .

C 1 .

C 2 .

C 3 .

;

run;

Some scores are missing beween some counters, and you want to put the same score as previous counter. So the result will look like below:

A 1 40

A 2 40

A 3 40

A 4 40

A 5 20

A 6 20

B 1 30

B 2 30

C 1 .

C 2 .

C 3 .

I managed to fill the missing score values forward by using the lag function like below:

data result1a;

set have(keep=user);

by user;

*Look ahead;

merge have have(firstobs=2 keep=score rename=(score=_NextScore));

if first.user then do;

if score= . then score=_NextScore;

end;

else do;

_PrevScore = lag(score);

if score= . then score=_PrevScore;

end;

output;

run;

Then I sorted the table backward by using descending funtion on counter like below:

proc sort data = result1a out= result1b;

by user descending counter ;

run;

Then finally I would fill the missing values forward in raaranged table (going backward according to the initial table) by using the lag function again like below.

I used the lag function in do-loop, because I wanted to update the previous value in each step (For example, the value 40 would be carried from the first score to the last score in the group all the way).

However, I get strange result. All missing values don't geta real value. Any idea about fixing the last data-step?

data result1c;

set result1b;

by user;

if first.user then do;

if score= . then score=_NextScore;

else score = score;

end;

else do;

_PrevScore = lag(score);

if score= . then

score=_PrevScore;

else score = score;

end;

output;

run;

解决方案

Don't need to use lag, use retain (or equivalent). Here's a double DoW loop solution that does it in one datastep (and, effectively, one read - it buffers the read so this is as efficient as a single read).

First we loop through the dataset to get the first score found, so we can grab that for the initial prev_score value. Then setting that, and re-looping through the rows for that user and outputting. There's no actual retain here since I am doing the looping myself, but it's similar to if there were a retain prev_score; and this was a normal data step loop. I don't actually retain it since I want it to go missing when a new user is met.

data want;

do _n_ = 1 by 1 until (last.user);

set have;

by user;

if missing(first_score) and not missing(score) then

first_score = score;

end;

prev_score = first_score;

do _n_ = 1 by 1 until (last.user);

set have;

by user;

if missing(score) then

score = prev_score;

prev_score = score;

output;

end;

run;