topic Re: Filling Gaps with value present in SAS Procedures

Filling Gaps with value present

robertrao — Wed, 16 Oct 2013 17:49:50 GMT

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

104 903

104 .

WANT

ID1 ID2

101 900

102 901

103 902

104 903

Re: Filling Gaps with value present

art297 — Wed, 16 Oct 2013 18:05:35 GMT

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

Re: Filling Gaps with value present

Linlin — Wed, 16 Oct 2013 18:44:54 GMT

easy for you to understand:

data have;

input ID1 : ID2;

cards;

101 .

101 900

101 .

102 .

102 901

102 .

102 901

103 .

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;

Re: Filling Gaps with value present

Haikuo — Wed, 16 Oct 2013 18:51:02 GMT

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.

Re: Filling Gaps with value present

Mit — Thu, 17 Oct 2013 02:08:14 GMT

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

Re: Filling Gaps with value present

art297 — Thu, 17 Oct 2013 02:35:39 GMT

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;