BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TongSu
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
sbxkoenk
SAS Super FREQ

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

View solution in original post

5 REPLIES 5
sbxkoenk
SAS Super FREQ

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
Calcite | Level 5
You code worked! Thank you, Koen!
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
TongSu
Calcite | Level 5
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
Tourmaline | Level 20
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 628 views
  • 2 likes
  • 4 in conversation