topic Re: keeping specific Obs by IDS in SAS Data Management

keeping specific Obs by IDS

GreenTree1 — Thu, 16 Jan 2020 03:48:45 GMT

HI,

I have two datasets.

data 1 data 2

id . infection id .

1 . no 1

1 . yes 2

2 . no 4

3 . yes

2 . no

4. no

the only IDs and observations I want to keep in Data 1 are the ones in data 2. So, in the end, I want Data 1 to look as follows

data 1 data 2

id . infection id .

1 . no 1

1 . yes 2

2 . no 4

2 . no

4. no

Since Id 3 is not in data 2, it has been removed. I also want to keep the multiple observations per id in data 1 and not limit data 1 to 1 obs per id. How do I go about this? would appreciate your help.

Thanks

Re: keeping specific Obs by IDS

Sathish_jammy — Thu, 16 Jan 2020 04:20:50 GMT

proc sort data = data1;
by id;
run;

data want;
merge data1(in=in1)
data2 (in=in2);
by id;
if in1 and in2;
run;

Re: keeping specific Obs by IDS

Ksharp — Thu, 16 Jan 2020 12:44:12 GMT

proc sql;
create table want as
select * from data1
 where id in (select id from data2);
quit;

Re: keeping specific Obs by IDS

GreenTree1 — Thu, 16 Jan 2020 17:42:56 GMT

Thank you Satish and Ksharp,

The code worked but it has shrunk my data more than expected. I just want to keep the IDs common to data1 and data2 and get rid of IDs in data 1 that are not in data2.

Data 1 has multiple observations tied to one ID and I want to keep those observations as well, whereas DATA 2 has one obs/id.

Re: keeping specific Obs by IDS

Patrick — Thu, 16 Jan 2020 21:46:56 GMT

@GreenTree1

Using the sample data you've posted the proposed solutions will both return the desired result you've posted.

If things are not working with your real data then there must be something different you haven't told us yet.

"Data 1 has multiple observations tied to one ID"

If you've got the ID repeated per observations as in your sample data then the proposed solutions will work. If they aren't repeated then you need to post sample data that's representative for your real data.

Re: keeping specific Obs by IDS

GreenTree1 — Mon, 20 Jan 2020 19:47:21 GMT

Thanks Patrick, I will try this on the original data and will update this post shortly.