I have the following data--how can I impute within the same ID and DAY the flag variable--if multiple flag values are available then take the first value and impute within the same ID and DAY--see example ID=3 and DAY=3:
data have;
input id day flag;
cards;
1 1 .
1 1 2
2 4 2
2 4 .
2 5 .
3 3 2
3 3 .
3 3 8
data wanted;
input id day flag;
cards;
1 1 2
1 1 2
2 4 2
2 4 2
2 5 .
3 3 2
3 3 2
3 3 8
data have;
input id day flag;
cards;
1 1 .
1 1 2
2 4 2
2 4 .
2 5 .
3 3 2
3 3 .
3 3 8
;
data want ;
do until (last.day) ;
set have ;
by id day ;
if flag and _n=. then _n = flag ;
end ;
do until (last.day) ;
set have ;
by id day ;
if flag=. then flag = _n ;
output ;
end ;
drop _n ;
run ;
it's not the most effcient code.
I do not manage to save the sort operation not even by putting tjhe notsorted option in the data step.
data have;
input id day flag;
cards;
1 1 .
1 1 2
2 4 2
2 4 .
2 5 .
3 3 2
3 3 .
3 3 8
;
run;
data have;
set have;
helper=missing(flag);
run;
proc sort data=have;
by id day helper;
run;
data want;
set have;
by id day helper;
retain lasty;
lasty=lag(flag);
if first.day then call missing(lasty);
if missing(flag) then flag=lasty;
drop helper lasty;
run;
data have;
input id day flag;
cards;
1 1 .
1 1 2
2 4 2
2 4 .
2 5 .
3 3 2
3 3 .
3 3 8
;
data want ;
if _n_ = 1 then do ;
dcl hash h (dataset:'have(where=(flag))' ) ;
h.definekey ('id', 'day') ;
h.definedata ('flag') ;
h.definedone ( ) ;
end ;
set have ;
if flag=. then _n_ = h.find( ) ;
run ;
proc sql;
create table WANT(drop=flag rename=(flag_=flag)) as
select id,day,flag,max(flag) as flag_
from have
group by 1,2;
quit;
Hi @Emma2021 @Reeza 's recommendation is spot on and very wise. Right, I am just posting for fun and to get my fingers back into some practice. I'd still suggest to go with Reeza's suggestion or use a DB specific SQL making use of the PARTIITON BY() option, enabling the use of min from each partition.
Anyways, for fun-
data have;
input id day flag;
cards;
1 1 .
1 1 2
2 4 2
2 4 .
2 5 .
3 3 2
3 3 .
3 3 8
;
proc sql;
create table want as
select a.id,a.day,coalesce(a.flag,b.flag) as flag
from have a
left join
(select *
from (select *,monotonic() as n from have)
group by id, day
having min(case when flag>. then n else . end)=n) b
on a.id=b.id and a.day=b.day;
quit;
I suggest you ask your local Oracle developers/admins.
data have;
input id day flag;
cards;
1 1 .
1 1 2
2 4 2
2 4 .
2 5 .
3 3 .
3 3 2
3 3 .
3 3 8
3 3 .
3 3 88
3 3 .
;
data temp;
set have;
n+1;
run;
data temp1;
set temp;
by id day;
retain new_flag;
if first.day then call missing(new_flag);
if not missing(flag) then new_flag=flag;
run;
proc sort data=temp1;by descending n;run;
data want;
set temp1;
by id day notsorted;
retain new_flag2;
if first.day then call missing(new_flag2);
if not missing(new_flag) then new_flag2=new_flag;
drop new_flag ;
run;
proc sort data=want;by n;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.