Filling missing character variable

[Bluejags]

I have a data that looks lie this:

Pat_ID City Sex 

012   1      

012  1     M

012 1     

015  2    F

015 2

016 1    M

016 1

I need to fill in the gender in the last column using the info available.  I am new and would appreciate any help.  The RETAIN statement works for PAT_ID 015 and 016 but not for the 012, as the sex info is missing in the first row.

[Reeza]

SQL is easiest here, as it allows for summary and row level calculations in a single code block - it still does the extra steps behind the scenes.

proc sql;
create table want as
select pat_id, city, coalesce(sex, max(sex)) as sex
from have
group by pat_id
order by 1, 2;
quit;

Otherwise something like this may work as well:

data gender;
set have;
where not missing(sex);
by pat_id;
if first.pat_id;
run;

data want;
merge have gender (rename=sex = sex_found);
by pat_id;
sex = coalescec(sex, sex_found);
run;

---

@Bluejags wrote:

I have a data that looks lie this:

Pat_ID City Sex 

012   1      

012  1     M

012 1     

015  2    F

015 2

016 1    M

016 1

I need to fill in the gender in the last column using the info available.  I am new and would appreciate any help.  The RETAIN statement works for PAT_ID 015 and 016 but not for the 012, as the sex info is missing in the first row.

---

[Bluejags]

Hi Reeza, the codes didnt work.  Still getting blank cells for gender.

[Ksharp]

data have;
infile cards truncover;
input Pat_ID City Sex $;
cards;
012   1      
012  1     M
012 1     
015  2    F
015 2
016 1    M
016 1
;

data want;
 merge have have(where=(new_sex is not missing) rename=(sex=new_sex));
 by Pat_ID City;
run;

[Bluejags]

Hi Ksharp,

The code did not work.  Here is the output.  Still the sex is missing from row. The log did not indicate any errors though. Am i doing something wrong?

[Reeza]

That's a screenshot of your HAVE data set not the newly created data set, WANT. Check your output again please.

EDIT: I just tested all three solutions posted (2 of mine and @Ksharp) all work as expected on the posted sample data.

[Bluejags]

MY BAD. Thanks Reeza for pointing out that i was not looking into the WANT and only the HAVE.  I am new to SAS  and didn't realize the output screen has option to click to look into all files generated for the set of codes.  

Also thanks to @Ksharp too.