Date: Fri, 13 Apr 2001 17:26:05 -0700
Reply-To: Biff Canlett <Biff@CANLETT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Biff Canlett <Biff@CANLETT.COM>
Organization: Posted via Supernews, http://www.supernews.com
Subject: Re: Data step merge or SQL join to behave like a format
There is an alternative that may apply. I'm sorry, but I don't have the
time now to give you a code sample. If the big is much larger than the
small, and this operation will be done many times, as in a transaction file
against a master file, indexed look up is KILLER. First index Big - one
time only.. Then set small, and in the same step set big. Its been several
years but I think the syntax is Set Big Index=<keyval>. You can use the
return value in conditional logic to tell you whether you got a hit, and if
not to set your defaults. I've done this on a 50k/10m match and it was
1/100th the cpu of a merge. Warning: more than one SET statement in the
Data Step sets all variables to RETAIN. You must null them out in your
code.
I hope this helps, not confuses!
"Nigel Tufnel" <dousk8@HOTMAIL.COM> wrote in message
news:F24ZbLAX3cf3ednHM3S000048de@hotmail.com...
> I'm sure that I am not the first person to ask this, but I could not find
> anything.
>
> I'd like a data step merge or SQL join to behave like a format. Here is
my
> situation:
>
> I have 2 data sets "Big" and "Small" both having a match key called "key".
> Id like to match small to big keeping all records in big and pullling a
few
> columns over from small (call them "s_var01" to "s_var10"). The problem
is
> that there are some keys values in big that do not exist in small. Rather
> than have s_var01 to s_var10 be missing after the merge/join, Id like
> s_var01 to s_var10 be "otherwise" values. In other words, in small I'd
have
> a catch all key value that should be used for all of the non-matches. So,
> for example, if big had a key value of "78" and small had no match for
"78",
> I like to pull the catch-all record from small onto big.
>
> See sample below
>
> Thanks,
> Nigel
>
> Sample:
>
> big
> =============
> key b_var01
> === =======
> 01 junk
> 01 junk
> 45 junk
> 52 junk
> 52 junk
> 52 junk
> 78 junk
> 85 junk
>
>
> small
> =============
> key s_var01
> === =======
> 01 stuff
> 02 stuff
> 03 stuff
> 45 stuff
> 52 stuff
> 85 stuff
> 99 NoMatch /* catch all no match value */
>
>
> Post merge
> ========================
> key b_var01 s_var01
> === ======= ========
> 01 junk stuff
> 01 junk stuff
> 45 junk stuff
> 52 junk stuff
> 52 junk stuff
> 52 junk stuff
> 78 junk NoMatch
> 85 junk stuff
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
| 
