Desktop productivity for business analysts and programmers

How can i search one table for an obs?

Posts: 1

How can i search one table for an obs?

i have a table "search" with one or more obs (variables A-M)
i also have a huge table "Sasuser.Data1" which has 1 million obs (variables A-M and Variables O and P)

what i need to do is search my DATA1 table for the obs in "search" and IF there is a match for ALL variables (A-M), then return variables O and P.

is this possible?

what is have so far is:

Libname sasuser;
options nodate pageno=1 linesize=80 pagesize=40;
proce compare compare = sasuser.data1 listequalvar;
Posts: 8,721

Re: How can i search one table for an obs?

Rather than use PROC COMPARE, I'd probably do an SQL join or a SAS merge with a BY statement. The only downside of the SAS merge is that both datasets need to be sorted by the variables in the BY statement. And, it's only a downside if you don't have enough memory to sort the big dataset.
Here's an SQL join example to create your table:
proc sql;
create table getsearch as
select srch.a, srch.b, srch.c, srch.d, srch.e, srch.f,
srch.g, srch.h, srch.i, srch.j, srch.k, srch.l
srch.m, big.o, big.p
from search as srch,
sasuser.data1 as big
where srch.a=big.a and
srch.b=big.b and
srch.c=big.c and
srch.d=big.d and
srch.e=big.e and
srch.f=big.f and
srch.g=big.g and
srch.h=big.h and
srch.i=big.i and
srch.j=big.j and
srch.k=big.k and
srch.l=big.l and

proc print data=getsearch;

If you need more help, you might consider contacting SAS Technical Support, because, depending on the size of your files and subsequent processing and whether you need to have the non-matches as well as the matches, it might turn out that a MERGE would work out better for you. They could help you understand how a Data Step MERGE differs from an SQL join. To find out how to contact Tech Support, refer to:

Good luck,
Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation