topic Re: Fill in missing IDs in SAS Programming

Fill in missing IDs

TongSu — Fri, 24 Sep 2021 15:12:01 GMT

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

Re: Fill in missing IDs

sbxkoenk — Fri, 24 Sep 2021 15:23:26 GMT

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

Re: Fill in missing IDs

TongSu — Fri, 24 Sep 2021 15:32:03 GMT

You code worked! Thank you, Koen!

Re: Fill in missing IDs

Jagadishkatam — Fri, 24 Sep 2021 15:53:59 GMT

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;

Re: Fill in missing IDs

PeterClemmensen — Fri, 24 Sep 2021 17:47:50 GMT

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;

Re: Fill in missing IDs

TongSu — Fri, 24 Sep 2021 18:00:04 GMT

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