Accepted Solutions
Editor's note: this is a popular topic. We've edited the solution to be more helpful for those facing a similar challenge. The first part here addresses the original question, and the second part addresses a more general LOCF (last observation carried forward) approach.
You apparently will go back one observation, but will go forward until a non-missing quantity is encountered, which was not how I understood your description. In other words, if the preceding observation is missing, then your task is a Next Observation Carried Back process.
I think the best way to approach this is to read an ID/DRUG group once and store quantity values into a array (_Q below). Then reread the group and when a missing quantity is encountered, read back from the array:
data want;
array _q {0:10} _temporary_;
do N=1 by 1 until (last.drug);
set have;
by id drug notsorted;
_q{N}=quantity;
end;
do I=1 to N;
set have;
do J= I-1 to N while (quantity=.);
quantity=_q{J};
end;
if quantity=. then quantity=28;
output;
end;
drop I J N;
run;
- Notes that the lower bound of the _Q array should be set to 0, so that "do J=I-1 to ..." works when I=1.
- Make sure the upper bound of array _Q will accommodate the longest series for single ID/DRUG group.
For the LOCF approach, @sarathannapareddy suggests the following.
Using 2 SET Statements in a single datastep:
Here is the code:data l4; set l2; n=_n_; if missing(quantity) then do; do until (not missing(quantity)); n=n-1; set l2(keep=quantity) point=n; *second SET statement; end; end; run;
For here's a comparison of the original data with the output for each of these approaches. The quantity_locf column is the result of @sarathannapareddy's LOCF approach. The quantity_next_rules column shows the output of @mkeintz's array approach that takes into account the OP's business rules.
Editor's note: this is a popular topic. We've edited the solution to be more helpful for those facing a similar challenge. The first part here addresses the original question, and the second part addresses a more general LOCF (last observation carried forward) approach.
You apparently will go back one observation, but will go forward until a non-missing quantity is encountered, which was not how I understood your description. In other words, if the preceding observation is missing, then your task is a Next Observation Carried Back process.
I think the best way to approach this is to read an ID/DRUG group once and store quantity values into a array (_Q below). Then reread the group and when a missing quantity is encountered, read back from the array:
data want;
array _q {0:10} _temporary_;
do N=1 by 1 until (last.drug);
set have;
by id drug notsorted;
_q{N}=quantity;
end;
do I=1 to N;
set have;
do J= I-1 to N while (quantity=.);
quantity=_q{J};
end;
if quantity=. then quantity=28;
output;
end;
drop I J N;
run;
- Notes that the lower bound of the _Q array should be set to 0, so that "do J=I-1 to ..." works when I=1.
- Make sure the upper bound of array _Q will accommodate the longest series for single ID/DRUG group.
For the LOCF approach, @sarathannapareddy suggests the following.
Using 2 SET Statements in a single datastep:
Here is the code:data l4; set l2; n=_n_; if missing(quantity) then do; do until (not missing(quantity)); n=n-1; set l2(keep=quantity) point=n; *second SET statement; end; end; run;
For here's a comparison of the original data with the output for each of these approaches. The quantity_locf column is the result of @sarathannapareddy's LOCF approach. The quantity_next_rules column shows the output of @mkeintz's array approach that takes into account the OP's business rules.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
-
35427 views
-
6 likes
-
-
