Filling out missing values in different scenarios

[hellorc]

Closed

[sbxkoenk · Accepted solution]

Something like this ?

PROC DATASETS LIBRARY=WORK NoList memtype=DATA;
 delete have: ; run;
 delete want  ; run;
QUIT;

data work.have;
input id hospital $ weight;
datalines;
1 A 55
1 B .
1 C 58
1 D .
1 E 59
2 A .
2 C .
2 F 65
2 G 69
3 B .
3 C 70
3 F .
4 C .
4 D .
;
run;

data work.startmissing   (keep=id) 
     work.startnonmissing(keep=id);
 set work.have;
 by id;
 if first.id then do;
  if weight = . then output work.startmissing;
  else               output work.startnonmissing;
 end;
run;

PROC SQL noprint;
 create table work.have1M as
 select * from work.have
 where id IN (select id from work.startmissing   )
 order by id , hospital desc;
 create table work.have2NM as
 select * from work.have
 where id IN (select id from work.startnonmissing)
 order by id , hospital ;
QUIT;

PROC EXPAND data=work.have1M 
             out=work.have1M_expout
			METHOD=STEP EXTRAPOLATE;
BY id; 
CONVERT weight = weightexp ; 
*ID hospital;
run; QUIT;
proc sort data=work.have1M_expout; by id hospital; run;
PROC EXPAND data=work.have2NM 
             out=work.have2NM_expout
			METHOD=STEP EXTRAPOLATE;
BY id; 
CONVERT weight = weightexp ; 
*ID hospital;
run; QUIT;
/* end of program */ 

PROC APPEND base=work.want data=work.have1M_expout ;
PROC APPEND base=work.want data=work.have2NM_expout;
PROC SORT data=work.want; by id hospital; run;
/* end of program */

Koen

[sbxkoenk]

Thanks for marking my response as the solution.

However ...

Keep in mind that this programme is entirely based on a missing value in the first record (for and id) or not.

• if no, missing value imputation is always done with the previous value
• If yes, missing value imputation is always done with the next value (previous value on the data in reverse order)
  STEP function in PROC EXPAND is used for these imputations.

With PROC TIMEDATA, you can set up mechanisms that are more intelligent.
PROC TIMEDATA is like a data step for time series data. You can look ahead to "future" records.
Entire time series (for a by-group) is taken in memory and at every time point t

, you have access to [ t - i ] AND ALSO to [ t + j ].

Best,

Koen

[hellorc]

Thank you, sbxkoenk! I like how you partition the data into start missing and startnonmissing, smart and easy to follow. I have never used proc timedata, might you be willing to show how it's done to produce the same output?

[sbxkoenk]

Hello @hellorc ,

---

@hellorc wrote:
I have never used proc timedata, might you be willing to show how it's done to produce the same output?

---

I will try to do that before the weekend.

Koen

[Reeza]

Is there a reason you are deleting your questions after answered?

They will be marked as solved, but if you delete them the thread becomes fairly useless to everyone else. Please do not delete your questions after they've been answered. It doesn't appear as if you're posting confidential data so that should not be an issue.