Hi, there
my data looks like this:
ID var1 var2
111 aaa 100
111 aaa 101
111 102
111
222 bbb 101
222 bbb 102
222 103
Just curious how can I fill the null var1 with previous row's value ony if var2 is not null and the desired data looks like:
want
ID var1 var2
111 aaa 100
111 aaa 101
111 aaa 102
111
222 bbb 101
222 bbb 102
222 bbb 102
Thanks in advance!
data have;
input ID var1 $ var2 ;
cards;
111 aaa 100
111 aaa 101
111 . 102
111 . .
222 bbb 101
222 bbb 102
222 . 103
;
data want;
set have;
by id;
k=lag(var1);
if not first.id and missing(var1) and k>' ' and var2 then var1=k;
drop k;
run;
data have;
input ID var1 $ var2 ;
cards;
111 aaa 100
111 aaa 101
111 . 102
111 . .
222 bbb 101
222 bbb 102
222 . 103
;
data want;
set have;
length new_var1 $ 80;
retain new_var1 new_var2;
by id;
if first.id then call missing(new_var1,new_var2);
if not missing(var1) then new_var1=var1;
if not missing(var2) then new_var2=var2;
if cmiss(new_var1,new_var2)=2 then call missing(new_var1,new_var2);
run;
What if you have consecutive instances of missing var1 but non-missing var2? I.e. what if you have
data have;
input ID var1 $ var2 ;
cards;
111 aaa 100
111 aaa 101
111 . 102
111 . 103
111 . .
222 bbb 101
222 bbb 102
222 . 103
;
I will only copy var1 when var 2 is not missing. missing var 1 is not irrelevant in this case, thanks!
What have you tried? Can you show your sincere attempt? I have noticed you haven't showed either in any of your threads and I think that's unfair
Try this.
data want;
set sample;
var1=ifc ((var1="" and var2>0),lag(var1),var1);
run;
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.