replace missing value with an array

[JNWong]

hi,

  as i want to replace mutiple variables' missing value by the previus value using an array.

i have tried the code below,  expected to replace three variables,(not create new variables): dev,legal,audit. which was defined as vars.

 however,it does not work.  and except for  other variables there only a new _dev variable in the output dataset. whose value is all missing. it is not my expected results.i do not need the new variables ,just replace the original variable' missing value.

%macro replace(n1=,id=,data=,n4=,vars=);
   %let m1 = %sysfunc(lag(&id));
   proc sort data = &n1 out = temp;
   by &id;
   run;
data &data;
   set temp;
   by &id;
   array miss(&n4) &vars;
   array missg(&n4);
   retain missg(&n4);
   do i = 1 to &n4;
%if &id = m1 %then %do;
missg(i)=miss(i);
else miss(i)=missg(i);
%end;
end; 
  drop of missg: i;
run;
%mend replace;
%replace(n1=temp,id=countryname,data=temp1,n4=3,vars=dev legal audit);

[Reeza]

Post sample data so we can test your code. 

Why are you using macro If/then loop? 

Also, in the IF/THEN you refer to m1, but are missing the ampersand, most likely should be &M1. 

[Reeza]

I also don't think M1 makes sense at all with a LAG function. 

It might be worth you commenting your code so we help you figure out where you went wrong. 

[JNWong]

i just want to replicate the code below to macro. the code below is replace the variable 'audit' missing value by the previous value.

DATA temp2;
   SET temp;
   by countryname;
   retain _audit;
   if countryname = lag(countryname) then do;
   if not missing(audit) then _audit=audit;
   else audit=_audit;
   end;
   drop _audit;
RUN;

[JNWong]

Thanks, i changed the m1 to &m1, but i still get the unexpected result. i am wondering if i should try the do over. id is countryname.

data temp3;
input countryname $1-5 +1 dev 1. +1 legal 1. +1 audit 1.;
datalines;
china . . .
china 0 0 5
china 0 0 5
china . . .
china . 5 9
japan . . .
japan 0 5 9
japan . . 5
japan 0 . .
;
run;

[Patrick]

Something like below could work.

data have;
  input countryname $1-5 +1 dev 1. +1 legal 1. +1 audit 1.;
datalines;
china . . .
china 0 0 5
china 0 0 5
china . . .
china . 5 9
japan . . .
japan 0 5 9
japan . . 5
japan 0 . .
;
run;

data want(drop=_i);
  set have;
  by countryname notsorted;

  array vars {3} dev legal audit;
  array lag_vars {3} 8 _temporary_;

  do _i=1 to dim(vars);
    if not first.countryname then vars[_i]=coalesce(vars[_i],lag_vars[_i]);
    lag_vars[_i]=vars[_i];
  end;

run;

[JNWong]

thanks,it is a good method.as i need a macro to replace the 'hardcode'.

[ChrisNZ · Accepted solution]

Like this?

%macro dolag(idvar=, lagvars=);
  %local var;
  %do i=1 %to %sysfunc(countw(&lagvars));
    %let var=%scan(&lagvars,&i);   
    _LAGVAR_&i=lag(&var);
    if lag(&idvar.)=&idvar. & missing(&var) then &var=_LAGVAR_&i;
  %end;
  drop _LAGVAR_:;
%mend;


data WANT;
  set HAVE;
  %dolag(idvar=COUNTRYNAME, lagvars=DEV LEGAL AUDIT);
run;

proc print noobs; 
run;

COUNTRYNAME	DEV	LEGAL	AUDIT
china	.	.	.
china	0	0	5
china	0	0	5
china	0	0	5
china	.	5	9
japan	.	.	.
japan	0	5	9
japan	0	5	5
japan	0	.	5

[JNWong]

thanks ,i have tried the code,but still have some missing values.

as for the dataset you have printed.

the last line 'japan 0 . 5  i hope to replace all the missing value within a group. 

so i tried the retain function.

[ChrisNZ]

You said: replace mutiple variables' missing value by the previus value
Not: replace mutiple variables' missing value by the previus non-missing value.
Which one is it?

[JNWong]

sorry,it is the latter one ,replace the missing value by the previous non-missing value.