topic Re: select matched data in SAS Programming

select matched data

lulu3 — Thu, 25 Jun 2020 23:24:41 GMT

I want to select all the information from data one based on the data two id.

data one;
input id n;
datalines;
101 a
101 b
101 c
101 d
102 a
102 c
103 f
104 f
105 f
105 u
;
run;

data two;
input id;
datalines;
101
104
105
;
run;

want:

101 a

101 b

101 c

104 f

105 f

105 u

Thanks!

Re: select matched data

Patrick — Thu, 25 Jun 2020 23:43:11 GMT

What have you tried already?

Either use a SQL inner join or a data step merge with the IN keyword.

merge A (in=ina) B;

by variable;

if ina;

...

Re: select matched data

ballardw — Thu, 25 Jun 2020 23:53:46 GMT

And is the

101 d

missing from your Want data set a typo? If not a type then you will need to describe a rule for identifying that the record should be excluded.

Re: select matched data

novinosrin — Fri, 26 Jun 2020 00:16:14 GMT

data one;
input id n $;
datalines;
101 a
101 b
101 c
101 d
102 a
102 c
103 f
104 f
105 f
105 u
;
run;

data two;
input id;
datalines;
101
104
105
;
run;

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

Re: select matched data

s_lassen — Fri, 26 Jun 2020 06:58:32 GMT

You have two good solutions suggested: the datastep by @Patrick, and the SQL by @novinosrin. What you should chose depends:

If your data TWO table only contains unique values of ID, the datastep is the fastest. This solution also assumes that both tables are sorted.
If there are multiple rows with the same ID in TWO, the SQL solution immediately gives the right solution, and may be simpler to implement. And the data does not need to be sorted. If data ONE is very large and not sorted, the SQL solution may actually be faster than the datastep (which also needs time for sorting the large table).