DATA Step, Macro, Functions and more

Delete cases who don't have before and after readings

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Delete cases who don't have before and after readings

[ Edited ]

Hi, 

 

I have a longitudinal data on Patients with hypertension  receiving a certain treatment. I would include the patient in the analysis , if and only if, he has two readings of blood pressure , one before treatment and one after. The data look like this 

 

ID     BloodPressure   Time

1        110                    Before

1         90                     After          

1         98                     After

2         90                      After

2         89                      After

3         95                      Before

3          97                     After

4          100                   before   

4           98                    Before 

5           89                    After 

 

As you can see , Pateints number 2 and 4 Will not be included as they don't have readings before and after. Patient number 5 will also be deleted as he only have one reading 

I only managed to delete cases with one reading using the command . 

 

Data new ;

set Have;

By ID;

If FIRST.ID and LAST.ID then delete; 

run;

 

Any suggestions  ?


Accepted Solutions
Solution
‎09-28-2016 12:43 AM
Respected Advisor
Posts: 4,927

Re: Delete cases who don't have before and after readings

Use a SQL query:

 

data have;
input ID     BloodPressure   Time :$8.;
datalines;
1        110                    Before
1         90                     After          
1         98                     After
2         90                      After
2         89                      After
3         95                      Before
3          97                     After
4          100                   before   
4           98                    Before 
5           89                    After 
;

proc sql;
create table want as
select * from have
group by ID
having count(distinct upcase(time)) > 1;
select * from want;
quit;

PG

View solution in original post


All Replies
Solution
‎09-28-2016 12:43 AM
Respected Advisor
Posts: 4,927

Re: Delete cases who don't have before and after readings

Use a SQL query:

 

data have;
input ID     BloodPressure   Time :$8.;
datalines;
1        110                    Before
1         90                     After          
1         98                     After
2         90                      After
2         89                      After
3         95                      Before
3          97                     After
4          100                   before   
4           98                    Before 
5           89                    After 
;

proc sql;
create table want as
select * from have
group by ID
having count(distinct upcase(time)) > 1;
select * from want;
quit;

PG
Occasional Contributor
Posts: 7

Re: Delete cases who don't have before and after readings

Thank you PG , I used it and it worked .

Super User
Posts: 11,343

Re: Delete cases who don't have before and after readings

Be advised that the code that @PGStats provided counts levels not values. If for some reason you end up with something like Atfer, Bfeore or other spellings or words like missing then it well include those.

Respected Advisor
Posts: 4,927

Re: Delete cases who don't have before and after readings

Yeah. I guess that counting DISTINCT UPCASE(FIRST(time)) would be a bit more robust.

PG
Occasional Contributor
Posts: 7

Re: Delete cases who don't have before and after readings

[ Edited ]

I have done both of them and end with same results. I created the Time variable myself, so I would not expect to have misspellings or errors. However, it's a valuable advice , I will take it into consideration in  other analyses. 

 

Thank you 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 333 views
  • 3 likes
  • 3 in conversation