sql

Poprawność numeru PESEL w SQL

Wednesday, November 19th, 2008 | Data Mining | No Comments

Algorytm znalazłem na stronie:
http://wipos.p.lodz.pl/zylla/ut/pesel.html

W pierwszym kroku tworzę tabelę tymczasową zapisując w kolumnach kolejne cyfry numeru pesel,

W drugim kroku tworzę tabelę z listą numerów oraz wynikiem testu.

proc sql;
create table pesele_temp as select
id,
pesel,
input(substr(trim(pesel),1,1),commax1.) as a1,
input(substr(trim(pesel),2,1),commax1.) as a2,
input(substr(trim(pesel),3,1),commax1.) as a3,
input(substr(trim(pesel),4,1),commax1.) as a4,
input(substr(trim(pesel),5,1),commax1.) as a5,
input(substr(trim(pesel),6,1),commax1.) as a6,
input(substr(trim(pesel),7,1),commax1.) as a7,
input(substr(trim(pesel),8,1),commax1.) as a8,
input(substr(trim(pesel),9,1),commax1.) as a9,
input(substr(trim(pesel),10,1),commax1.) as a10,
input(substr(trim(pesel),11,1),commax1.) as a11
from tabela
;quit;

proc sql;
create table pesele_v as select
id,
pesel,
ifn (mod( a1 * 9 +
a2 * 7 +
a3 * 3 +
a4 * 1 +
a5 * 9 +
a6 * 7 +
a7 * 3 +
a8 * 1 +
a9 * 9 +
a10* 7
,10)=a11, 1, 0) as TEST
from pesele_temp
;quit;