SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Changing gender to match patient ID number

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Changing gender to match patient ID number

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                 F

212                 U

212                 F

1102              M

1102               . 

 

Thank you! 


Accepted Solutions
Solution
‎05-26-2016 03:14 PM
Respected Advisor
Posts: 4,663

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

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;
PG

View solution in original post


All Replies
Super User
Posts: 10,552

Re: Changing gender to match patient ID number

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?

Super User
Posts: 5,099

Re: Changing gender to match patient ID number

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

Occasional Contributor
Posts: 15

Re: Changing gender to match patient ID number

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

 

Occasional Contributor
Posts: 15

Re: Changing gender to match patient ID number

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

Super User
Posts: 17,963

Re: Changing gender to match patient ID number

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. 

Occasional Contributor
Posts: 15

Re: Changing gender to match patient ID number

Yes, the data is ordered by date.
Occasional Contributor
Posts: 15

Changing multiple gender observations to fit a unique patient identifier

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                 F

212                 U

212                 F

1102              M

1102               . 

 

Thank you! 

Solution
‎05-26-2016 03:14 PM
Respected Advisor
Posts: 4,663

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

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;
PG
Occasional Contributor
Posts: 15

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

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

Super User
Posts: 17,963

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

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;
Super User
Posts: 5,099

Re: Changing gender to match patient ID number

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;

Super User
Posts: 9,691

Re: Changing gender to match patient ID number

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 752 views
  • 5 likes
  • 6 in conversation