DATA Step, Macro, Functions and more

replace missing value with an array

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

replace missing value with an array

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

 


Accepted Solutions
Solution
‎02-20-2017 02:37 AM
PROC Star
Posts: 1,566

Re: replace missing value with an array

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

 

View solution in original post


All Replies
Super User
Posts: 17,912

Re: replace missing value with an array

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. 

Super User
Posts: 17,912

Re: replace missing value with an array

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. 

Contributor
Posts: 44

Re: replace missing value with an array

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;
Contributor
Posts: 44

Re: replace missing value with an array

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;
Respected Advisor
Posts: 3,900

Re: replace missing value with an array

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;
Contributor
Posts: 44

Re: replace missing value with an array

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

Solution
‎02-20-2017 02:37 AM
PROC Star
Posts: 1,566

Re: replace missing value with an array

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

 

Contributor
Posts: 44

Re: replace missing value with an array

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.

 

 

PROC Star
Posts: 1,566

Re: replace missing value with an array

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?
Contributor
Posts: 44

Re: replace missing value with an array

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 210 views
  • 0 likes
  • 4 in conversation