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);
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 |
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.
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.
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;
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;
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;
thanks,it is a good method.as i need a macro to replace the 'hardcode'.
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 |
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.
sorry,it is the latter one ,replace the missing value by the previous non-missing value.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.