Removing select cases from a dataset

[Angi]

Hi,

I have two datasets in long format that have a variable “ID” in common.

If DatasetB doesn’t have a matching ID with DatasetA, I want to delete unmatched cases in DatasetB.

Is there any way to do this without merging the two datasets?

Have DatasetA:

ID	VarA	VarB
1	Abcd	1232
1	Erfsdjdgr	94847
2	Fakjsdf;asie	5342
3	Kjdafjdh	0589576
3	Hfajdhfdh	143

Have DatasetB:

ID	VarX	VarY
2	124363	Abjdkafh
2	5463	Giagaojdfl
3	9378464	Nadfaei
5	23756	Nann
6	987543	Ueyrf
7	625364	Qodiafe

Want new_DatasetB

ID	VarX	VarY
2	124363	Abjdkafh
2	5463	Giagaojdfl
3	9378464	Nadfaei

Thank you for your help!

[PGStats · Accepted solution]

The simplest way (create a new table, no sorting) would be:

proc sql;

create table new_datasetB as

select * from datasetB

where ID in (select ID from datasetA);

quit;

if you would prefer to modify datasetB (could be faster if datasetB is large) :

proc sql;

delete from datasetB

where ID not in (select ID from datasetA);

quit;

PG

[Angi]

Thank you very much!