topic Re: replace missing value with an array in SAS Programming

replace missing value with an array

JNWong — Mon, 20 Feb 2017 03:31:43 GMT

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);

Re: replace missing value with an array

Reeza — Mon, 20 Feb 2017 03:56:46 GMT

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.

Re: replace missing value with an array

Reeza — Mon, 20 Feb 2017 03:58:25 GMT

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.

Re: replace missing value with an array

JNWong — Mon, 20 Feb 2017 04:07:46 GMT

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;

Re: replace missing value with an array

JNWong — Mon, 20 Feb 2017 04:13:48 GMT

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;

Re: replace missing value with an array

Patrick — Mon, 20 Feb 2017 04:34:14 GMT

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;

Re: replace missing value with an array

ChrisNZ — Mon, 20 Feb 2017 04:36:46 GMT

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

Re: replace missing value with an array

JNWong — Mon, 20 Feb 2017 05:43:31 GMT

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.

Re: replace missing value with an array

ChrisNZ — Mon, 20 Feb 2017 06:11:24 GMT

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?

Re: replace missing value with an array

JNWong — Mon, 20 Feb 2017 06:15:32 GMT

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

Re: replace missing value with an array

JNWong — Mon, 20 Feb 2017 07:26:25 GMT

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