Fill in missing IDs

[TongSu]

data have;

input ID Sex Injury_Type;

dataline

1 1 1

   1 2

   1 3

2 2 1

   2 3

;

Data want;

input ID Sex Injury_Type;

dataline

1 1 1

1  1 2

1  1 3

2 2 1

2 2 3

;

Thanks

Su

[sbxkoenk · Accepted solution]

Hello,

That's the LVCF-method (Last Value Carried Forward aka forward filling).

You find dozens of topic-threads on that subject within these communities.

Because your variables are all numeric, you can use PROC EXPAND (SAS/ETS).

data have;
input ID Sex Injury_Type;
datalines;
1 1 1
. 1 2
. 1 3
2 2 1
. 2 3
;
run;

PROC EXPAND data=have out=want(drop=time)
            method=STEP EXTRAPOLATE;
 convert _NUMERIC_;
run;
/* end of program */

[EDIT] Take care. This way (above) they are all (i.e. all 3 variables) filled. Maybe you just want

convert ID; 

statement.

Koen

[TongSu]

You code worked! Thank you, Koen!

[Jagadishkatam]

Alternatively you may try 

data have;
input ID Sex Injury_Type;
datalines;
1 1 1
. 1 2
. 1 3
2 2 1
. 2 3
;
run;

data want;
set have(rename=(id=_id));
by sex Injury_Type notsorted;
retain id;
if first.sex then id=.;
if _id ne . then id=_id;
run;

[TongSu]

Thanks for the alternative idea. My actual data is much messier than my example, both sex and injury type had random missing values and they are in text format too.
I wonder how to handle it if I also have missing value in Sex and Intury_Type, for example the data have look like this:

data have;
input ID Sex Injury_Type;
datalines;
1 1
. 1 2
. 1
2 2 1
. 3
;
run;

It's a learning experience to me, I appreciate your ideas.

Thanks,
Su

[PeterClemmensen]

data have;
input ID Sex Injury_Type;
datalines;
1 1 1
. 1 2
. 1 3
2 2 1
. 2 3
;
run;

data want;
   set have;
   if ID then _iorc_ = ID;
   else ID = _iorc_;
run;