Help using Base SAS procedures

Filling Gaps with value present

Reply
Super Contributor
Posts: 1,040

Filling Gaps with value present

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

PROC Star
Posts: 7,363

Re: Filling Gaps with value present

: 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;

Super Contributor
Posts: 1,636

Re: Filling Gaps with value present

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;

Respected Advisor
Posts: 3,124

Re: Filling Gaps with value present

Just like Art said, it will be many ways Smiley Happy,

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.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Filling Gaps with value present

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

PROC Star
Posts: 7,363

Re: Filling Gaps with value present

and, of course, there is always proc sql:

proc sql;

  create table want (drop=_Smiley Happy as

    select id1,_id2,max(_id2) as id2

      from have (rename=(id2=_id2))

        group by id1

  ;

quit;

Ask a Question
Discussion stats
  • 5 replies
  • 261 views
  • 0 likes
  • 5 in conversation