BookmarkSubscribeRSS Feed
Smitha9
Fluorite | Level 6

Hi,

I have a dataset

ID  Name         DOB

1   Sarah    07/29/1970

1       .                .

1   Sarah      07/29/1970

 

I want to fill the missing values and dataset which I have has 10000's ID's.

ID  Name         DOB

1   Sarah    07/29/1970

1   Sarah     07/29/1970               

1   Sarah      07/29/1970

 

can I do this in SAS?

 

thanks in advance.

3 REPLIES 3
PaigeMiller
Diamond | Level 26

From now on, @Smitha9 please provide data as working SAS data step code, as I show here. Please test your code to confirm that it actually works. What you have provided is not SAS data step code.

 

data have;
input ID  Name $8. DOB :anydtdte.;
infile cards missover;
cards;
1   Sarah    07/29/1970
1                
1   Sarah    07/29/1970
;

 

Here is my solution

 

data want;
    set have;
    retain name1 dob1;
    if not missing(name) then name1=name;
    if not missing(dob) then dob1=dob;
    drop name dob;
    format dob1 mmddyy10.;
run;

 

--
Paige Miller
Kurt_Bremser
Super User

It seems that one also needs to take ID into account:

data have;
input ID Name :$8. DOB :mmddyy10.;
format dob yymmdd10.;
infile cards missover;
cards;
1 Sarah 07/29/1970
1  .
1 Sarah 07/29/1970
;

data want;
set have;
by id;
retain _name _dob;
if first.id or not missing(name)
then _name = name;
else name = _name;
if first.id or not missing(dob)
then _dob = dob;
else dob = _dob;
drop _dob _name;
run;
Astounding
PROC Star

Here's a different approach.  It has a major advantage in that it fills in missings for all your variables:

data want;
   update have (obs=0) have;
   by id;
   output;
run;

So the program remains short no matter how many variables you have.  The disadvantage:  you need a sorted data set for this to work.  Another consideration:  it requires extra work if you want to fill in missings for only some of your variables but not for others.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 778 views
  • 2 likes
  • 4 in conversation