How can i search one table for an obs?

sssb2000 — Sun, 04 Mar 2007 05:38:41 GMT

hi,
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 base=work.search compare = sasuser.data1 listequalvar;
??????
run;

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

Cynthia_sas — Mon, 05 Mar 2007 17:52:39 GMT

Hi!
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:
[pre]
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
srch.m=big.m;
quit;

proc print data=getsearch;
run;
[/pre]

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:
http://support.sas.com/techsup/contact/index.html

Good luck,
cynthia

topic Re: How can i search one table for an obs? in SAS Enterprise Guide

How can i search one table for an obs?

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