Data Quality CZ - portál věnující se tématu kvalitních dat

Porovnávání řetězců s využitím nástroje BASE SAS– 1. díl

[1.1.2012] D. Pejčoch

Úvod

Porovánávání a slučování představuje jednu z typických činností v rámci řízení datové kvality, jenž se uplatňuje zejména v souvislosti s vytvářením unifikovaných bází kmenových dat (Master Data Management). V rámci této činnosti jsou nově příchozí záznamy porovnávány s referenčními záznamy uloženými v bázi a je rozhodováno, zda je dosaženo takové míry shody mezi nově vstupujícím záznamem a tím referenčním, aby bylo možné považovat oba záznamy za totožné. Typickým příkladem je rozhodování, zda nově zakládaného klienta již obsahuje CDI hub, či ne. Pro tyto účely je možné použít řadu technik, které lze klasifikovat následujícím způsobem:

Použtí výše uvedených metod je často provázeno aplikací fonetických algoritmů jako je Soundex, NYSIIS či Metaphone kódujících shodným způsobem podobně znějící hlásky. Aplikace fonetických pravidel je obsažena mimo jiné přímo v algoritmech vytváření porovnávacích kódů. Podrobněji se jednotlivými algoritmy zabývám v rámci studie [1], na níž odkazuji případné zájemce o hlubší detail. V tomto seriálu uvedu vždy jen nutné minimum pro pochopení funkcionality dostupné v rámci nástroje BASE SAS. U čtenáře předpokládám znalost základních stavebních prvků SASího kódu (procedury, data step, macro language, hash object, formáty, ...) a orientaci v pojmech používaných v souvislosti s prostředím SAS.

Prostý SQL Join / Lookup

Jak již bylo naznačeno, přímé použití prostého SQL Join / Lookup je omezeno na případy, kdy máme k dispozici jednoznačný primární klíč, v rámci něhož nepřipouštíme výskyt chyb. V praxi se s případem, kdy do databáze přichází nový záznam, který je možné porovnat na základě jedinečného identifikátoru s referenční bází, setkáme např. u RČ nebo IČO. Mnohem častějším případem je však absence takového identifikátoru.

Přehled funkcionality pro přesný Join /Lookup poskytovaný v rámci nástroje BASE SAS společně s výčtem výhod a nevýhod jejich použití obsahuje tabulka č. 1.

Tabulka 1: Funkcionalita pro přesný Join / Lookup
Název funkcionality Příklad syntaxe Výhody / Nevýhody

PROC SQL

(implementace ANSI SQL v nástroji SAS)

proc sql;
create table vystup as
select a.*, b.*
from dpejcoch.data1 as a
left join dpejcoch.data2 as b
on a.icof = b.icof;
quit;

Výhody:

  • Lze použít i pro nepřesný „fuzzy“ join využitím sounds-like operatoru =* nebo použitím měr podobnosti v rámci WHERE a ON klauzule (např. funkce SPEDIS()), nebo používáním HAVING COUNT(*) syntaxe (viz Fuzzy Join);
  • PK spojovaných relací může mít různý název;
  • Lze spojit tabulky a vytvořit report v jednom kroku bez nutnosti vytvářet data set;
  • Není potřeba sortovat data před aplikací PROC SQL;

Nevýhody:

  • SQL JOIN je založen na uplatnění WHERE podmínky na výsledek kartézského součinu;
  • Kartézský součin při N:M;
  • Obecně potřebuje více CPU času a paměti než DATA STEP;
  • Nelze joinovat více než 32 tabulek najednou J;
  • Možná úskalí představuje kombinace ON a WHERE;

DATA STEP MERGE

(funkcionalita pro sekvenční procházení datových souborů s možností jejich vertikálního i horizontálního slučování)
data vystup;
merge vstup1 (IN = In1)
vstup2 (IN = In2 (WHERE = (...)));
by icof;
if In1 = 1;
run;

Modifikace OUTER / INNER Join lze provádět pomocí podmínek:
  • Inner: IF In1 = 1 AND In2 = 1;
  • Left Outer: IF In1 = 1;
  • Right Outer: IF In2 = 1;
  • Full Outer: nic nebo IF In1 = 1 OR In2 = 1;

Výhody:

  • Obecně potřebuje méně CPU času a paměti než PROC SQL;
  • Složité konstrukce v těle DATA STEPu;
  • Lze kombinovat s HASH OBJECT + FORMAT + ARRAY;

Nevýhody:

  • Specifické chování při vazbě M:N;
  • Data musí být oindexována nebo seřazena podle BY proměnné;
  • Použitelné pouze pro přesný join;
  • BY názvy atributů, přes které probíhá zřetězování, musí být v každém data setu shodné;
  • Přepsání stejně nazvaných proměnných bez varování;

DATA STEP MERGE BY KEY

data vystup;
set data;
set RES key = icof;
do;
if _IORC_ then do;
_ERROR_ = 0;
FIRMA = '';
NAZ23F = '';
PSCF = '';
end;
end;
run;

Výhody:

  • Obě tabulky nemusí mít index;
  • Z lookup tabulky jsou přečtena pouze nezbytná pozorování;
  • Může vrátit i více hodnot z lookup tabulky;
  • Velmi šetrné na paměť;

Nevýhody:

  • Alespoň jedna z tabulek musí mít index;
  • Pouze pro přesný join;
  • _IORC_ konstrukci je nutné použít pro kontrolu nezřetězených záznamů;
  • Nelze použít s WHERE klauzulí na vstupu jedné z tabulek => jen pro lookup a relaci 1:1;

Format

(Funkcionalita využitelná pro Lookup do číselníků)
libname formats "e:\saslibs\dpejcoch\";

data res (rename = (icof = start firma = label));
set dpejcoch.res;
retain fmtname 'res' type 'c';
run;

proc format cntlin=res library=formats.dpejcoch; run;

options fmtsearch=(formats.dpejcoch);

data vystup;
set vstup;
firma = put(icof,$res.);
run;

Výhody:

  • Lze použít uvnitř DATA STEPu i PROC SQL;

Nevýhody:

  • Lze použít jen pro LOOKUP jedné proměnné;
  • Pokud nenajde v LOOKUP tabulce, vloží LOOKUP klíč;

Array

data vystup (keep = a ICOF FIRMA NAZ23F PSCF);
array aICOF(3000000) $8;
array aFIRMA(3000000) $255;
array aNAZ23F(3000000) $255;
array aPSCF(3000000) $5;
retain z i (0 0);
do until (lasta);
set dpejcoch.res end=lasta;
z + 1;
aFIRMA(z) = FIRMA;
aNAZ23F(z) = NAZ23F;
aPSCF(z) = PSCF;
end; do until (lastb);
set dpejcoch.data end=lastb;
FIRMA = '';
NAZ23F = '';
PSCF = '';
do i = 1 to z;
if ICOF = aICOF(i) then do;
FIRMA = aFIRMA(i);
NAZ23F = aNAZ23F(i);
PSCF = aPSCF(i);
end;
end;
output;
end;
stop;
run;

Výhody:

  • Vždy rychlejší než LOOKUP pomocí hash object a formátem;
  • Možnost použít nesortovaný / neindexovaný data set;

Nevýhody:

  • Náročnost na paměť;
  • Prvek pole musí být určen numerickými hodnotami;
  • Použitelné pouze pro LOOKUP – vrací pouze jednu hodnotu;
  • Existuje pouze po dobu trvání aktuálního data stepu v rámci něhož je inicializováno;

Hash Object

data vystup;
if _N_ = 1 then do;
if 0 then set dpejcoch.res (keep = icof firma naz23f pscf);
declare hash res(dataset:'dpejcoch.res');
res.defineKey('icof');
res.defineData('firma', 'naz23f', 'pscf');
res.defineDone();
end;
set data;
flag = res.find();
if flag ^= 0 then do;
firma = '';
naz23f = '';
pscf = '';
end;
run;

Výhody:

  • Lookup se odehrává v paměti – žádné nákladné přístupy k datům;
  • Pouze malé množství lookup operací – do výskytu první shody;
  • Možné i více lookup atributů;
  • Alokuje pouze tolik paměti, kolik potřebuje;
  • Není třeba sortovat, indexovat;
  • Současně lze více lookupů přes různé klíče;
  • Existuje jen po dobu spuštění data stepu;

Nevýhody:

  • Lze použít pouze pro lookup, nikoliv pro merge 1:N;
  • Problém při missing hodnotách v lookup tabulce;

Fuzzy Join

Přibližný „fuzzy“ join je možné použít jako alternativu přesného joinu v případech, kdy nepředpokládáme přesnou shodu porovnávaného klíče. V nástroji SAS jej lze realizovan třemi různými způsoby v rámci PROC SQL: (1) aplikací operátoru pro přibližnou rovnost, (2) aplikací HAVING COUNT(*) kontrukce a (3) aplikací míry podobnosti v rámci WHERE klauzule. Pro demonstraci konkrétních případů uvažujme následující dva data sety:

t1
ab
1David
2Dvid
3Pavid
4Dan
5Pavel
t2
cd
1David
2Petr
3Daniel

Aplikace přibližné rovnosti

Přibližnou rovnost lze při porovnávání několika data setů v rámci where kaluzule aplikovat pomocí symbolu =*. Výsledkem níže uvedeného kódu je tabulka vzniklá spojením prvního a druhého záznamu z t1 s prvním záznamem z t2, neboť řetězce David a Dvid byly vyhodnoceny jako dostatečně podobné.

proc sql;
create table vystup as
select t1.*, t2.*
from t1, t2
where t1.b=* t2.d;
quit;

Aplikace HAVING COUNT(*) konstrukce

Před aplikaci další metody je nejprve nutné rozložit porovnávané řetězce z tabulky t1 na tokeny shodné délky tak, jak ukazuje tabulka t3. Současně je nutné obdobným způsobem upravit záznam z referenční báze (viz tabulka t4).

t3
ab
1dav
1avi
1vid
2dvi
2vid
3pav
3avi
3vid
4dan
5pav
5ave
5vel
t4
cd
Daviddav
Davidavi
Davidvid

Kód uvedený níže označí za vhodné kandidáty ke sloučení s referenčním záznamem David vstupní záznamy 1 a 3 z tabulky t3, neboť v jejich případě došlo k vyšší shodě v počtu tokenů, než je minimum vymezené pomocí klauzule having calculated score < 1.

proc sql;
create table tst as
select t3.a, t4.c, count(*) as score
from t3, t4
where t3.b = t4.d
group by 1,2
having calculated score > 1;
quit;

Aplikace míry podobnosti v rámci WHERE klauzule

Funkce spedis měří editační vzdálenost (tj. počet nutných operací smazání, vložení, záměnu) při transformaci t1.b na t2.d. Níže uvedený kód opět aplikujeme na tabulky t1 a t2. Při nastavení prahové hodnoty 10 dojde pouze ke sloučení scela totožných řetězců "David". Až postupným zvyšováním prahu na úroveň 20 dojde ke stejnému sloučení jako v prvním příkladu.

proc sql;
create table tst as
select t1.*, t2.*
from t1, t2
where
spedis(t1.b,t2.d) < 10;
quit;

Pro zajímavost jsem níže připojil tabulku, která ukazuje vypočtenou editační vzdálenost pro všechny kombinace porovnávaných atributů z t1 a t2.

Míry podobnosti mezi jednotlivými záznamy z t1 a t2
DavidDavid0
DvidDavid12
PavidDavid40
DanDavid66
PavelDavid80
DavidPetr107
DvidPetr125
PavidPetr67
DanPetr150
PavelPetr67
DavidDaniel50
DvidDaniel75
PavidDaniel90
DanDaniel50
PavelDaniel70

Závěr

V prvním díle jsem velmi stručně seznámil s problematikou porovnávání řetězců a základními technikami pro jeho praktickou realizaci. Podrobně jsem uvedl výhody a nevýhody použití jednotlivých přístupů pro přesný join / merge / lookup v rámci nástroje SAS a též popsal tři různé přístupy v rámci fuzzy join. V příštím díle se budu zabývat metrikami založenými na úpravách. Ukáži další funkce, které je možné v SASu použít pro výpočet editační vzdálenosti. Seznámíme se také se základy SAS Macro Language a ukážeme si, jak pomocí něj vytvořit funkce vlastní.

Použitá literatura

Komentáře ke článku

Stránka byla naposledy aktualizována dne 4.5.2015
Powered by HOLOPAGE
©2011 - 2015 D. Pejčoch