Help using Base SAS procedures

Replacing missing values by previous observation

Reply
Occasional Contributor
Posts: 16

Replacing missing values by previous observation

I have a data where patients were prescribed medication. I have four columns labelled ID (for a patient), prescibeddate (date of prescription), drug ( name of the treatment) and quantity ( number of pills). In some patients I have the quantity missing. What I will like to do is to replace the missing quantity with the immediate previous quantity if the previous drug is the same in that patient. OR replace the missing quantity with the next if the next drug is the same. Otherwise the missing quantity is replaced by the standard quantity which is 28 for both treatment A and treatment B. I am working on a large data set. But as an example the original data is of the following format;

ID prescribeddate drug quantity
1 08/jan/2008 A 28
1 08/feb/2008 A 14
1 08/Mar/2008 A
1 08/Apr/2008 B 56
1 08/May/2008 B
2 08/Jan/2008 A 30
2 08/Mar/2009 B
2 28/Mar/2008 A 30
2 08/Mar/2008 B 14
3 08/Jan/2008 B
3 08/Mar/2009 B 56
3 08/Mar/2008 A
4 08/jan/2008 A
4 08/feb/2008 A
4 08/Mar/2008 A 14
4 08/Apr/2008 B
4 08/May/2008 B


I want the new data to look as follows:

ID prescribeddate drug quantity
1 08/jan/2008 A 28
1 08/feb/2008 A 14
1 08/Mar/2008 A 14
1 08/Apr/2008 B 56
1 08/May/2008 B 56
2 08/Jan/2008 A 30
2 08/Mar/2009 B 28
2 28/Mar/2008 A 30
2 08/Mar/2008 B 14
3 08/Jan/2008 B 56
3 08/Mar/2009 B 56
3 08/Mar/2008 A 28
4 08/jan/2008 A 14
4 08/feb/2008 A 14
4 08/Mar/2008 A 14
4 08/Apr/2008 B 28
4 08/May/2008 B 28

I believe i have made myself clear. Can anyone help. Thank you.
Occasional Contributor
Posts: 11

Re: Replacing missing values by previous observation

you can use retain function.
Regular Contributor
Posts: 184

Re: Replacing missing values by previous observation

Contributor
Posts: 66

Re: Replacing missing values by previous observation

data l2;
input ID prescribeddate$ 3-14 drug$ quantity;
cards;
1 08/jan/2008 A 28
1 08/feb/2008 A 14
1 08/Mar/2008 A .
1 08/Apr/2008 B 56
1 08/May/2008 B .
2 08/Jan/2008 A 30
2 08/Mar/2009 B .
2 28/Mar/2008 A 30
2 08/Mar/2008 B 14
3 08/Jan/2008 B .
3 08/Mar/2009 B 56
3 08/Mar/2008 A .
4 08/jan/2008 A .
4 08/feb/2008 A .
4 08/Mar/2008 A 14
4 08/Apr/2008 B .
4 08/May/2008 B .
;
run;


data l3;
set l2;
retain tot;
if quantity>. then tot=quantity;
run;
Occasional Contributor
Posts: 5

Re: Replacing missing values by previous observation

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;

Sarath Annapareddy
http://studysas.blogspot.com/2008/10/lag-function-how-to-obtain-information.html
Contributor
Posts: 57

Re: Replacing missing values by previous observation

data MasterRec;
do until(last.drug);
set Curr_data;
by drug quantity;
if last.quantity and not missing(quantity) then do;
q = quantity;
end;
if missing(quantity) then do;
quantity=q;
end;
output;
end;
run;
Ask a Question
Discussion stats
  • 5 replies
  • 13933 views
  • 3 likes
  • 6 in conversation