Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Re: How do I delete multiple observations/rows based on one instance o...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 10-18-2020 07:10 PM
(2531 views)

Hi all,

Somewhat new to repeated measures but I'm working on a dataset that has repeated data. Below is a sample:

ID | Visit | Label |

AAA | 1 | XY |

AAA | 2 | Z |

AAA | 3 | . |

BBB | 1 | XY |

BBB | 2 | XY |

CCC | 1 | Z |

DDD | 1 | . |

DDD | 2 | . |

DDD | 3 | XY |

EEE | 1 | Z |

EEE | 2 | . |

FFF | 1 | XY |

GGG | 1 | Z |

HHH | 1 | . |

HHH | 2 | Z |

HHH | 3 | XY |

I want to delete any IDs where a label of XY appears at least once across any visit. The code I have tried below only seems to delete the exact rows that has an XY label, and not every row that belongs to that ID:

```
data want;
set have;
by id;
if label = "XY" then delete;
run;
```

Essentially, what I'd want to do is produce a table below that removes all other rows of that ID:

ID | Visit | Label |

CCC | 1 | Z |

EEE | 1 | Z |

EEE | 2 | . |

GGG | 1 | Z |

Any help would be much appreciated!

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
data have;
input
ID $
Visit $
Label $;
cards;
AAA
1
XY
AAA
2
Z
AAA
3
.
BBB
1
XY
BBB
2
XY
CCC
1
Z
DDD
1
.
DDD
2
.
DDD
3
XY
EEE
1
Z
EEE
2
.
FFF
1
XY
GGG
1
Z
HHH
1
.
HHH
2
Z
HHH
3
XY
;
proc sql;
create table want as
select *
from have
where id not in (select id from have where label="XY");
quit;
```

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
data have;
input
ID $
Visit $
Label $;
cards;
AAA
1
XY
AAA
2
Z
AAA
3
.
BBB
1
XY
BBB
2
XY
CCC
1
Z
DDD
1
.
DDD
2
.
DDD
3
XY
EEE
1
Z
EEE
2
.
FFF
1
XY
GGG
1
Z
HHH
1
.
HHH
2
Z
HHH
3
XY
;
proc sql;
create table want as
select *
from have
where id not in (select id from have where label="XY");
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi @novinosrin ! Thanks so much for helping me out. The code works perfectly! I definitely need to look into learning more about proc sql.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

A SQL DELETE statement will remove rows from the data set without rewriting the whole table.

proc sql undo_policy=none; delete from have where ID in (select ID from have where label='XY') ; quit;

⏰

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.** **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.