BookmarkSubscribeRSS Feed
Denali
Quartz | Level 8

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
4 REPLIES 4
Shmuel
Garnet | Level 18

data want;

 set have;

   by SubjectID;

        if  first.subjectid and last subjectid then output; else

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

run;

kiranv_
Rhodochrosite | Level 12

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 = .;
Astounding
PROC Star

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.

mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 471 views
  • 0 likes
  • 5 in conversation