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.
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.
| 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:
Nevýhody:
|
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:
|
Výhody:
Nevýhody:
|
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:
Nevýhody:
|
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:
Nevýhody:
|
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:
Nevýhody:
|
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:
Nevýhody:
|
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:
| a | b |
|---|---|
| 1 | David |
| 2 | Dvid |
| 3 | Pavid |
| 4 | Dan |
| 5 | Pavel |
| c | d |
|---|---|
| 1 | David |
| 2 | Petr |
| 3 | Daniel |
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;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).
| a | b |
|---|---|
| 1 | dav |
| 1 | avi |
| 1 | vid |
| 2 | dvi |
| 2 | vid |
| 3 | pav |
| 3 | avi |
| 3 | vid |
| 4 | dan |
| 5 | pav |
| 5 | ave |
| 5 | vel |
| c | d |
|---|---|
| David | dav |
| David | avi |
| David | vid |
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;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.
| David | David | 0 |
| Dvid | David | 12 |
| Pavid | David | 40 |
| Dan | David | 66 |
| Pavel | David | 80 |
| David | Petr | 107 |
| Dvid | Petr | 125 |
| Pavid | Petr | 67 |
| Dan | Petr | 150 |
| Pavel | Petr | 67 |
| David | Daniel | 50 |
| Dvid | Daniel | 75 |
| Pavid | Daniel | 90 |
| Dan | Daniel | 50 |
| Pavel | Daniel | 70 |
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í.