|
"Bubbles" <bb@noclient.net> wrote in message
news:9ccab57b.0401231241.673a0ade@posting.google.com...
> Hi. SAS Newbie here.
>
> We've got this really big table with many columns we'd like to update
> (add a new column from a look up file). The key on the lookup file is
> only part of the key on the big file. I have the below SQL code which
> appears will work fine, but I was wondering if there is a better way
> of doing this (sure there must be) where I don't have to then drop the
> additional field created and then replace the original big file
> manually? This code also appears to run forever...
>
> The goal is to add new field CODE of LOOKUP to table BIG when
> KEY1(1,12)=XKEY1 OF LOOKUP
>
> LOOKUP:
> KEY1 $12
> CODE $3
>
> BIG
> KEY $15
> 20+ other fields I need to keep
>
>
> My code which is still running .. not even sure this will work.
>
> proc sql;
> create table BIGNEW as
> select *
> from BIG, LOOKUP
> where
> XKEY1 = substr(KEY1,1,12)
> order by KEY1;
> run;
>
> ... I would then have to drop XKEY1 from BIGNEW and overlay BIG with
> BIGNEW (again looking for streamline way of doing both of these as
> well).
>
> Thanks.
First, you are not updating anything. You are Proc SQL inner joining (or
DATA Step by group merging) two tables.
You can do the same thing with a custom format. If things are still too
slow, you will have to advance to functional mapping or hashing techniques.
This code demonstrates using custom formats to perform lookup.
* generate ten keys with distinct lookups;
data lookup;
length key $12 code $3;
do until (i>=10);
i + 1;
key = substr (put(ranuni(1),14.12),3);
code = byte(64+i);
output;
end;
stop;
drop i;
run;
* generate data containing 100 instances of each key in lookup,
* plus 1,000 observations unlikely to meet the key matching criteria;
data big;
do until (end);
set lookup(keep=key rename=(key=shortkey)) end=end;
do i=1 to 100;
key = shortkey || substr(put(ranuni(2),5.3),3);
array v v1-v20;
do j = lbound(v) to hbound(v);
v{j} = ranuni(2);
end;
output;
end;
end;
do i = 1 to 1000;
key = substr(put(ranuni(2),17.15),3);
do j = lbound(v) to hbound(v);
v{j} = ranuni(2);
end;
output;
end;
stop;
drop i j shortkey;
run;
* create a custom format to map the 15 char key to the
* code of the corresponding 12 char key prefix.;
data format;
set lookup(rename=(key=start code=label));
end=start||repeat(byte(255),12);
fmtname='$prefix';
run;
proc format cntlin=format;
run;
* create a view to deliver the big data
* with a new variable containing the corresponding 12char key code;
data big2 / view=big2;
retain key code;
set big;
code = put(key,$prefix3.);
* Note: if $prefix does not map key, then code will be
* first three chars of key. So if key and its mapping are
* the same, then the key does not have a lookup code;
if key^=:code;
run;
--
Richard A. DeVenezia
http://www.devenezia.com/downloads/sas/macros/
| 
