Hi,
I want to fill the missing gaps.
ID2 is present atleast once and i want to fill it where it is missing?????
HAVE
ID1 ID2
101 .
101 900
101 .
102 .
102 901
102 .
102 901
103 .
103 902
103 902
103 902
104 903
104 .
WANT
ID1 ID2
101 900
101 900
101 900
102 901
102 901
102 901
102 901
103 902
103 902
103 902
103 902
104 903
104 903
easy for you to understand:
data have;
input ID1 : ID2;
cards;
101 .
101 900
101 .
102 .
102 901
102 .
102 901
103 .
103 902
103 902
103 902
104 903
104 .
;
proc sort data=have(where=(not missing(id2))) nodupkey out=temp;
by id1;
data want;
merge have(drop=id2) temp;
by id1;
proc print;run;
Just like Art said, it will be many ways ,
data want;
merge have have(rename=id2=_id2 where=(not missing(_id2)));
by id1;
id2=coalesce(id2,_id2);
drop _id2;
run;
Haikuo
update: if id2 is Char, then you need to use coalescec() instead.
Data have;
input ID1 ID2
;
datalines;
101 .
101 900
101 .
102 .
102 901
102 .
102 901
103 .
103 902
103 902
103 902
104 903
104 .
;
run;
proc sort data=have ;
by ID1 descending ID2;
run;
data want;
set have;
by ID1;
retain ID2_new;
if not missing(ID2) then ID2_new=ID2;
drop ID2;
rename ID2_new=ID2;
run;
Output
ID1 | ID2 |
101 | 900 |
101 | 900 |
101 | 900 |
102 | 901 |
102 | 901 |
102 | 901 |
102 | 901 |
103 | 902 |
103 | 902 |
103 | 902 |
103 | 902 |
104 | 903 |
104 | 903 |
and, of course, there is always proc sql:
proc sql;
create table want (drop=_:) as
select id1,_id2,max(_id2) as id2
from have (rename=(id2=_id2))
group by id1
;
quit;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.