topic Re: Filling missing character variable in SAS Programming

Filling missing character variable

Bluejags — Mon, 25 Oct 2021 19:34:11 GMT

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.

Re: Filling missing character variable

Reeza — Mon, 25 Oct 2021 19:45:05 GMT

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.

Re: Filling missing character variable

Ksharp — Tue, 26 Oct 2021 12:21:46 GMT

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;

Re: Filling missing character variable

Bluejags — Tue, 26 Oct 2021 14:31:41 GMT

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?

Re: Filling missing character variable

Bluejags — Tue, 26 Oct 2021 14:50:08 GMT

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

Re: Filling missing character variable

Reeza — Tue, 26 Oct 2021 15:27:38 GMT

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.

Re: Filling missing character variable

Bluejags — Tue, 26 Oct 2021 15:50:43 GMT

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.