topic Re: How to delete duplicate subjects for the data contain missing value? in SAS Programming

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

Denali — Tue, 06 Jun 2017 19:49:09 GMT

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!

Obs	SubjectID	VISIT	Age	Race	HighestGrade	InterviewerID	TodaysDate	Quality_Of_Life
1	2031	Baseline	.	.	.	28	1-Nov-12	8
2	2031	Baseline	55	4	7	28	1-Nov-12	9

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

Shmuel — Tue, 06 Jun 2017 20:02:41 GMT

data want;

set have;

by SubjectID;

if first.subjectid and last subjectid then output; else

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

run;

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

kiranv_ — Tue, 06 Jun 2017 20:35:40 GMT

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 = .;

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

Astounding — Tue, 06 Jun 2017 20:38:26 GMT

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.

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

mkeintz — Wed, 07 Jun 2017 21:02:05 GMT

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;