topic Re: Changing multiple gender observations to fit a unique patient identifier in SAS Data Management

Changing gender to match patient ID number

mphqueens — Thu, 26 May 2016 17:05:08 GMT

I am looking for a solution to my current problem.

SAS 9.4

I have a variable with patient IDs and another variable of gender. The patient IDs are entered multiple times but the corresponding genders are not always entered or are "unidentified" (U). I would like to find a way to change all the genders to the same if they fall under the same patient ID.

Ex.

Patient ID Gender

104 M

104 U

104 M

212 F

212 U

212 F

1102 M

1102 .

Thank you!

Changing multiple gender observations to fit a unique patient identifier

mphqueens — Thu, 26 May 2016 17:02:15 GMT

I am looking for a solution to my current problem.

SAS 9.4

Ex.

Patient ID Gender

104 M

104 U

104 M

212 F

212 U

212 F

1102 M

1102 .

Thank you!

Re: Changing gender to match patient ID number

ballardw — Thu, 26 May 2016 17:21:07 GMT

Do you have any that look like this ?

104 M

104 U

104 F

If so what rule would you like for selecting between M and F?

Re: Changing gender to match patient ID number

Astounding — Thu, 26 May 2016 17:27:19 GMT

These days, it's possible that all three values were valid at the time they were recorded.

Re: Changing gender to match patient ID number

mphqueens — Thu, 26 May 2016 17:33:30 GMT

Not that I have found! It seems that they are consistent with either M or F with some U and blanks.

Re: Changing gender to match patient ID number

mphqueens — Thu, 26 May 2016 17:34:23 GMT

However, if I found any with M and F and U, I would assume the first recorded gender would be the correct one.

Re: Changing multiple gender observations to fit a unique patient identifier

PGStats — Thu, 26 May 2016 18:04:33 GMT

To implement the policy : keep consistent gender assignment:

data have;
input Patient_ID    Gender $;
datalines;
104                 M
104                 U
104                 M
212                 F
212                 F
212                 U
212                 F
1102              M
1102               . 
999 M
999 F
999 .
999 U
;

proc sql;
create table gender as
select 
	patient_id, 
	case
		when count(distinct Gender) = 1 then max(Gender)
		else ""
		end as gender
from have
where Gender in ("M", "F")
group by patient_id;
select * from gender;
quit;

Re: Changing multiple gender observations to fit a unique patient identifier

Reeza — Thu, 26 May 2016 18:08:04 GMT

Here's a quick SQL query that will set all to the M/F observed assuming only one value is observed. If you have both a M/F for a single patient_id then that will break this code. You'll need to determine how to handle those cases if they occur in your data.

data have;
infile cards truncover;
input Patient_ID $   Gender $;
cards;
104                 M
104                 U
104                 M
212                 F
212                 F
212                 U
212                 F
1102                M
1102               
;
run; 

proc sql;
create table want as
select a.patient_id, b.gender
from have as a
left join (select distinct patient_id, gender from have where not missing(gender) and gender ne "U") as b
on a.patient_id=b.patient_id
order by 1, 2;
quit;

Re: Changing gender to match patient ID number

Astounding — Thu, 26 May 2016 18:30:45 GMT

If you're more comfortable with a DATA step approach, here's how it could be done:

data want;

length first_gender $ 1;

do until (last.patient_id);

set have;

by patient_id;

if first_gender=' ' and gender in ('F', 'M') then first_gender = gender;

end;

if first_gender=' ' then first_gender=gender;

do until (last.patient_id);

set have;

by patient_id;

gender = first_gender;

output;

end;

drop first_gender;

run;

Re: Changing multiple gender observations to fit a unique patient identifier

mphqueens — Thu, 26 May 2016 19:13:26 GMT

This worked perfectly...thank you! Is there a way to merge this table back to my original data set?

Re: Changing gender to match patient ID number

Reeza — Thu, 26 May 2016 19:23:54 GMT

Do you have a date that goes along with the data? How can you identify which is the first/last besides order which isn't a good way.

Given the world today, it may be more appropriate to use the last gender, rather than first.

Re: Changing gender to match patient ID number

mphqueens — Thu, 26 May 2016 19:29:54 GMT

Yes, the data is ordered by date.

Re: Changing gender to match patient ID number

Ksharp — Fri, 27 May 2016 03:24:11 GMT

data have;
infile cards truncover;
input Patient_ID $   Gender $;
cards;
104                 M
104                 U
104                 M
212                 F
212                 F
212                 U
212                 F
1102                M
1102               
;
run; 
proc sort data=have(keep=Patient_ID Gender where=(Gender in ('F' 'M'))) out=temp nodupkey;
by Patient_ID Gender ;
run;
proc sort data=have;by Patient_ID ;run;
data want;
 merge have(drop=Gender) temp;
 by Patient_ID ;
run;