Filling Gaps with value present

[robertrao]

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

[art297]

: lots of ways.  Here is one way:

data want;

  set have (in=a rename=(id2=_id2)) have (drop=id2);

  by id1;

  retain id2;

  if first.id1 then id2=_id2;

  else if a then do;

    if not missing(_id2) then id2=_id2;

  end;

  else output;

run;

[Linlin]

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;

[Haikuo]

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.

[Mit]

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

[art297]

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;