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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.