topic Re: imputation missing values in SAS Programming

imputation missing values

sahoositaram555 — Wed, 18 Mar 2020 13:33:59 GMT

Hi i have a table like below

data dummy;
input subjid parcat $ AVAL;
cards;
001 CA 5
001 CB .
001 CC .
002 DA 8
002 DB .
003 EA 9
003 EB .
003 EC .
003 ED .
;
run;

i want to impute the values of 5 till the end of subjid 001 , similarly for imputation of value 8 for the subjid 002 and so on. any ideas how to go about doing it by retain statement or any other smooth procedure?

Re: imputation missing values

novinosrin — Wed, 18 Mar 2020 13:37:06 GMT


data dummy;
input subjid parcat $ AVAL;
cards;
001 CA 5
001 CB .
001 CC .
002 DA 8 
002 DB . 
003 EA 9 
003 EB . 
003 EC . 
003 ED .
;
run;

data want;
 update dummy(obs=0) dummy;
 by subjid;
 output;
run;

Re: imputation missing values

PeterClemmensen — Wed, 18 Mar 2020 13:39:35 GMT

Try this

data Want;
   update dummy(obs=0) dummy;
   by subjid;
   output;
run;

Re: imputation missing values

sahoositaram555 — Wed, 18 Mar 2020 20:09:35 GMT

Hi later i saw in the dataset shome places that it looks something like below.

data dummy;
input subjid parcat $ AVAL;
cards;
001 CA .
001 CB .
001 CC 5
002 DA .
002 DB 6
002 DB .
003 EA 9
003 EB .
003 EC 8
003 ED .
004 FA .
004 FB .
004 FC 7
004 FD .
004 FE .
;
run;
any ideas how to imput the values correctly for each subject ids?

Re: imputation missing values

PeterClemmensen — Wed, 18 Mar 2020 20:18:45 GMT

Try this

data want (drop=_AVAL);
   _AVAL = .;
   do _N_=1 by 1 until (last.subjid);
      set dummy;
      by subjid;
      if AVAL > _AVAL then _AVAL = AVAL;
   end;
   do _N_ = 1 to _N_;
      set dummy;
      AVAL = _AVAL;
      output;
   end;
run;

Re: imputation missing values

novinosrin — Wed, 18 Mar 2020 20:29:53 GMT

In my opinion, if you just have one non missing value, a SQL self join is very convenient and easy to maintain



data dummy;
input subjid parcat $ AVAL;
cards;
001 CA .
001 CB .
001 CC 5
002 DA . 
002 DB 6
002 DB . 
003 EA 9 
003 EB . 
003 EC 8 
003 ED .
004 FA .
004 FB .
004 FC 7
004 FD .
004 FE .
;
run;

proc sql;
create table want as
select subjid,parcat,max(AVAL) as aval
from dummy
group by subjid;
quit;

Re: imputation missing values

sahoositaram555 — Wed, 18 Mar 2020 20:30:47 GMT

Hi, the dataset looks blank. any ideas why? or any other method?

Re: imputation missing values

sahoositaram555 — Wed, 18 Mar 2020 20:35:08 GMT

hi @novinosrin, thanks .it worked