How to delete duplicate subjects for the data contain missing value?

Reply
Occasional Contributor
Posts: 10

How to delete duplicate subjects for the data contain missing value?

Hi,

 

I have 121 subjects in total and they have questionnaire data in 3 time points (baseline, 6 month and 12 month). I found subject ID = 2031 has two baseline data, and I want to delete the one has age, race, education level missing value. Could you please help me with the code? Thank you!

  

ObsSubjectIDVISITAgeRaceHighestGradeInterviewerIDTodaysDateQuality_Of_Life
12031Baseline...281-Nov-128
22031Baseline5547281-Nov-129
Super User
Posts: 1,244

Re: How to delete duplicate subjects for the data contain missing value?

data want;

 set have;

   by SubjectID;

        if  first.subjectid and last subjectid then output; else

        if age=. or race=. or highestgrade=. then delete;

run;

Regular Contributor
Posts: 233

Re: How to delete duplicate subjects for the data contain missing value?

try this

data have ;
input id visit $ val1 val2 val3;
datalines;
1 Baseline . . .
1 Baseline 2 3 4
1 faseline . . .
2 Baseline . . .
;



proc sql;
delete  from have a 
where exists
(select * from have b
where a.id =b.id
and a.visit='Baseline'
group by id
having count(id)> 1)
and a.val1 = .
and a.val2 =.
and a.val3 = .;
Respected Advisor
Posts: 4,998

Re: How to delete duplicate subjects for the data contain missing value?

Schmuel's approach would work, but might require some changes (depending on what the data looks like on the 6 month and 12 month visits).  Assuming you have sorted your data set by SubjectID Visit DESCENDING Age:

 

data want;

set have;

by subjectID Visit descending Age;

if first.visit;

run;

 

If you are concerned about more than one duplicate, we might have to revisit how to subset properly.

Super User
Posts: 789

Re: How to delete duplicate subjects for the data contain missing value?

Assuming, for each subject id, there is always exactly one record with non-missing age,race,highestgrade, then a single WHERE statement will work, no matter how many record is one or more of those variables missing:

 

 

data want;

   set have;

   where n(age,race,highestgrade)=3;

run;

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 107 views
  • 0 likes
  • 5 in conversation