segunda-feira, 10 de setembro de 2012

Macro SAS - SCD I e II

%let APPDIR=%SYSGET(NOME_DIRETORIO);

%include "&APPDIR\PROG\GECRPDeclaraLibNames.sas";

%macro GESCD(TipoSCD, TipoChv,
NomeBiblStg, TabStg, CmpChaveStg, CmpRetornoStg,
NomeBiblDim, TabDim, CmpChaveSkDim, CmpChaveDim,
NomeCmpDatIniVigDim, NomeCmpDatFimVigDim );
/*
    PROGRAMA SAS
         PROJ: Tarefas gerais de ETL.
         DESC: Realiza a tarefa de SCD tipos I e II  a partir de uma tabela de stage e uma dimensão.
         AUTOR: Erick Furst - erick.furst@coresynesis.com.br
         DATA: 2009-09-08
     INPUTS/OUTPUTS
        INs : Nome biblioteca, tabela, campo(s) chave de negócio e campo(s) de retorno da tabela
stage: NomeBiblStg, TabStg, CmpChaveStg, CmpRetornoStg.
 Nome biblioteca, tabela, campo(s) chave substituta, campo(s) chave de negócio e campo(s) de retorno da
tabela de dimensão: NomeBiblDim, TabDim, CmpChaveSkDim, CmpChaveDim.
 Obs.: (Só poderá ser informado um campo para a variável "CmpChaveSkDim".)
        OUTs: Não há.
    MODIFICACOES
        2009-09-10 - Erick Furst - erick.furst@coresynesis.com.br
             (+) Alteração para realização de SCD Tipo I sem surrogate Key.
        2009-09-12 - Erick Furst - erick.furst@coresynesis.com.br
             (+) Alteração para correção do campo de suurogate key que estava fixo qdo deveria utilizar a variável.
*/
/*-------------------------------------------------------------------------------------------------------------------*/

/* Armazena Data Atual do sistema para gravação do controle de histórico do SCD tipo 2. */
%let DataAtual = today( );

/* Recupera o valor da maior Sk armazenada na dimensão. */

%if &TipoChv = 'S' %then %do;
proc sql;
SELECT MAX( &CmpChaveSkDim ) into : Maior FROM &NomeBiblDim..&TabDim ;
quit;
%end;

/* Recupera o tipo e o tamanho de dados dos campos da tabela de Stage. Já no formato para trabalho no DATA STEP.
E armazena na tabela WORK.TamStg*/
proc sql;
CREATE TABLE WORK.TamStg AS
select 'length ' || NAME || ' ' || CASE TYPE WHEN 'char' THEN '$' ELSE '' END || TRIM(PUT(LENGTH,6.)) || ';' AS TamCol
from dictionary.columns
where UPPER(libname)= UPPER("&NomeBiblStg")
and UPPER(MEMNAME) = UPPER("&TabStg");
quit;

/* Recupera o tipo e o tamanho de dados dos campos da tabela de Dimensão. Já no formato para trabalho no DATA STEP.
E armazena na tabela WORK.TamDim*/
proc sql;
CREATE TABLE WORK.TamDim AS
select 'length ' || NAME || ' ' || CASE TYPE WHEN 'char' THEN '$' ELSE '' END || TRIM(PUT(LENGTH,6.)) || ';' AS TamCol
from dictionary.columns
where UPPER(libname)= UPPER("&NomeBiblDim")
and UPPER(MEMNAME) = UPPER("&TabDim");
quit;


/* Desmembra a String armazenada na variável &CmpChaveStg (Campos chave da tabela de Stage)
para identificação dos campos. E concatenação posterior na string do call execute que gerará o código do lookup. */
data WORK.ChaveStg;

CmpChaveStg = &CmpChaveStg;
CmpChaveStgTra = CmpChaveStg;
CmpChaveStgLst = CmpChaveStgTra;
Pos = 1;
PosIni = 1;
PosFim = length(CmpChaveStgTra);
OrdemStg = 0;

do while (Pos ^= 0);
Pos = INDEX(CmpChaveStgTra, ",");
if Pos = 0 then
 CmpChaveStgLst = SUBSTR(CmpChaveStgTra, PosIni, PosFim);
else do;
  CmpChaveStgLst = SUBSTR(CmpChaveStgTra, PosIni, Pos - 1);
  CmpChaveStgTra = SUBSTR(CmpChaveStgTra, Pos+1 , PosFim - Pos);
       PosIni=1;
  PosFim = length(CmpChaveStgTra);
end;
OrdemStg = OrdemStg + 1;
output;
end;

drop CmpChaveStgTra; drop Pos; drop PosIni; drop PosFim;

run;

/* Desmembra a String armazenada na variável &CmpChaveDim (Campos chave da tabela de Dimensão)
para identificação dos campos. E concatenação posterior na string do call execute que gerará o código do lookup. */
data WORK.ChaveDim;

CmpChaveDim = &CmpChaveDim;
CmpChaveDimTra = CmpChaveDim;
CmpChaveDimLst = CmpChaveDim;*AQUI;

Pos = 1;
PosIni = 1;
PosFim = length(CmpChaveDimTra);
OrdemDim = 0;

do while (Pos ^= 0);
Pos = INDEX(CmpChaveDimTra, ",");
if Pos = 0 then
      CmpChaveDimLst = SUBSTR(CmpChaveDimTra, PosIni, PosFim);
else do;
      CmpChaveDimLst = SUBSTR(CmpChaveDimTra, PosIni, Pos - 1);
 CmpChaveDimTra = SUBSTR(CmpChaveDimTra, Pos+1 , PosFim - Pos);
 PosIni=1;
 PosFim = length(CmpChaveDimTra);
end;
OrdemDim = OrdemDim + 1;
output;
end;


drop CmpChaveDimTra; drop Pos; drop PosIni; drop PosFim;

run;


data _null_;

length TamCol $100;
length TxtLengthDim $1000;

declare hash LstTamColDim(dataset:"WORK.TamDim");

LstTamColDim.defineKey("TamCol");
LstTamColDim.defineData("TamCol");
LstTamColDim.defineDone();

declare hiter iterDim('LstTamColDim');

crTamCol = iterDim.first();

do while (crTamCol=0);

TxtLengthDim = CATS(TxtLengthDim, TamCol);
crTamCol = iterDim.next();

end;

drop TamCol; drop crTamCol;

call symput('LengthDim',TxtLengthDim);

run;


/* SCD Tipo I & II */
/* *************** */
data _null_;

/* Tratamento de campos. */
/* ********************* */
/* Definição de padrão para substituição com a função "rxparse". Uso necessário para passagem de
campos de parâmetro separados por vírgula. A substituição de caracteres é feita através da função
"rxchange" e também é feita a limpeza de caracteres brancos através da função "compress". */


rx=rxparse("$',;' to '"",""'"); /* Definição de padrão para substituição. */

* Chave da Tabela de Stage;

length CmpChaveStgCon $1000;
CmpChaveStgCon = &CmpChaveStg || '                                                                                                ';
call rxchange(rx,length(CmpChaveStgCon),CmpChaveStgCon); /* Substituição de valor */
CmpChaveStgCon=compress(CmpChaveStgCon); /* Eliminação de espaços brancos. */

* Campos de retorno da Tabela de Stage;
length CmpRetornoStgCon $1000;
CmpRetornoStgCon = &CmpRetornoStg || '                                                                                                ';
call rxchange(rx,length(CmpRetornoStgCon),CmpRetornoStgCon); /* Substituição de valor */
CmpRetornoStgCon=compress(CmpRetornoStgCon); /* Eliminação de espaços brancos. */

* Chave da Tabela de Dimensão;
length CmpChaveDimCon $1000;
CmpChaveDimCon = &CmpChaveDim || '                                                                                                ';
call rxchange(rx,length(CmpChaveDimCon),CmpChaveDimCon); /* Substituição de valor */
CmpChaveDimCon=compress(CmpChaveDimCon); /* Eliminação de espaços brancos. */


rx=rxparse("$',""' to ' '"); /* Definição de padrão para substituição. */

CS = CmpChaveStgCon;
call rxchange(rx,length(CS),CS); /* Substituição de valor */

CD = CmpChaveDimCon;
call rxchange(rx,length(CD),CD); /* Substituição de valor */


call symput('ChvStg',CS);
call symput('ChvDim',CD);


/* SCD Tipo I & II (Criação de tabela de Update e Insert) */
/* ****************************************************** */
length TxtInUp $5000;

TxtInUp = 'data WORK.SCDI WORK.SCDU; ';

/* O cursor abaixo monta o comando lenght para a tabela de Stage.*/
length TamCol $100;
length TxtLengthStg $1000;

declare hash LstTamColStg(dataset:"WORK.TamStg");

LstTamColStg.defineKey("TamCol");
LstTamColStg.defineData("TamCol");
LstTamColStg.defineDone();

declare hiter iterStg('LstTamColStg');

crTamCol = iterStg.first();

do while (crTamCol=0);

TxtLengthStg = CATS(TxtLengthStg, TamCol);
crTamCol = iterStg.next();

end;

drop TamCol; drop crTamCol;

/* Concatenação dos blocos da string na variável final TxtCodLookup. */
TxtInUp = CATS(TxtInUp, ' ');
TxtInUp = CATS(TxtInUp, TxtLengthStg);


TxtInUp2 =

%if &TipoChv = 'S' %then %do;
symget('CmpChaveSkDim') || ' = ' || symget('Maior') || '; ' ||
'if MISSING(' || symget('CmpChaveSkDim') || ') then ' || symget('CmpChaveSkDim') || ' = 0; ' ||
%end;
'declare hash Stage(dataset:"'|| symget('NomeBiblStg') || '.' || symget('TabStg') ||
'", ordered: "' || TRIM(CmpChaveStgCon) || '"); ' ||
'Stage.defineKey("' || TRIM(CmpChaveStgCon) || '"); ' ||
'Stage.defineData("' || TRIM(CmpRetornoStgCon) || '"); ' ||
'Stage.defineDone(); ' ||
'declare hash Dimensao(dataset:"' || symget('NomeBiblDim') || '.' || symget('TabDim') ||
'", ordered: "' || TRIM(CmpChaveDimCon) || '"); ' ||
'Dimensao.defineKey("' || TRIM(CmpChaveDimCon) || '"); ' ||
'Dimensao.defineData("' || TRIM(CmpChaveDimCon) || '"); ' ||
'Dimensao.defineDone(); ' ||
'declare hiter iterStage("Stage"); ' ||
'rc = iterStage.first(); ' ||
'do while (rc=0); ';

TxtInUp = CATS(TxtInUp, TxtInUp2);


/* O cursor abaixo monta o comando de comparação entre o(s) campo(s) chave da tabela de Dimensão com o(s)
campo(s) chave da tabela de Stage. Para a posterior recuperação de campos feito pelo comando FIND do objeto HASH.
Ex.: CampoChaveTabelaDimensão = CampoChaveTabelaStage; */
length OrdemStg 3; length CmpChaveStgLst $32;

declare hash LstStg(dataset:"WORK.ChaveStg");

LstStg.defineKey("OrdemStg");
LstStg.defineData("OrdemStg","CmpChaveStgLst");
LstStg.defineDone();

declare hiter iterChvStg('LstStg');

length OrdemDim 3; length CmpChaveDimLst $32;

declare hash LstDim(dataset:"WORK.ChaveDim");

LstDim.defineKey("OrdemDim");
LstDim.defineData("OrdemDim","CmpChaveDimLst");
LstDim.defineDone();

cursor = iterChvStg.first();

do while (cursor=0);

OrdemDim = OrdemStg;
cursor = LstDim.find();
TxtInUp = CATS(TxtInUp, CmpChaveDimLst || ' = ' || CmpChaveStgLst || '; ');

cursor = iterChvStg.next();

end;

drop CmpChavePesLst; drop CmpChaveManLst; drop OrdemPes; drop OrdemStg; drop cursor;


TxtInUp3 =
%if &TipoChv = 'S' %then %do;
' if &TipoSCD = 2 then do ' ||

symget('CmpChaveSkDim') || ' = ' || symget('CmpChaveSkDim') || ' + 1; ' ||
' DataIniVig = &DataAtual; ' ||
' DataFimVig = -21914; ' ||
' end; ' ||
%end;

' if Dimensao.FIND() = 0 then ' ||
' output WORK.SCDU; ' ||
' else do; ' ||
%if &TipoChv = 'S' %then %do;
' if &TipoSCD = 1 then ' || symget('CmpChaveSkDim') || ' = ' || symget('CmpChaveSkDim') || ' + 1; ' ||
%end;
' output WORK.SCDI; ' ||
' end; ' ||

' rc = iterStage.next(); ' ||
'end; ' ||

'drop rc; ' ||

'run; ';

TxtInUp = CATS(TxtInUp, TxtInUp3);

/* Gera e executa o código de SCD para criação de tabela de insert e update. */
CALL EXECUTE (TxtInUp);

drop _All_;

run;


%if &TipoSCD = 1 %then %goto Tipo1;
%else %goto Tipo2;

%Tipo1:
/* Preparação Tipo I */
/* ***************** */

data WORK.SCDU;
set WORK.SCDU;
&LengthDim;
drop %if &TipoChv = 'S' %then %do; &CmpChaveSkDim %end; &NomeCmpDatIniVigDim &NomeCmpDatFimVigDim;
run;

data WORK.SCDI;
set WORK.SCDI;
&LengthDim;
drop &NomeCmpDatIniVigDim &NomeCmpDatFimVigDim;
run;

/* Atualização Tipo I */
/* ****************** */

data &NomeBiblDim..&TabDim;
   modify &NomeBiblDim..&TabDim WORK.SCDU;
   by &ChvDim;
   if _iorc_ = %sysrc(_sok) then replace;
   else _ERROR_=0;
run;

%goto Saida;

%Tipo2:
/* Atualização Tipo II */
/* ******************* */

data WORK.SCDData;
set WORK.SCDU;
&LengthDim;
&NomeCmpDatFimVigDim = &DataAtual;
keep &ChvDim &NomeCmpDatFimVigDim;
run;

proc sort data=&NomeBiblDim..&TabDim;
   by &ChvDim descending &NomeCmpDatIniVigDim;
run;

data &NomeBiblDim..&TabDim;
   modify &NomeBiblDim..&TabDim WORK.SCDU;
   by &ChvDim;
   if _iorc_ = %sysrc(_sok) then replace;
   else _ERROR_=0;
run;

/* Inserção Tipo II */
/* **************** */
proc append base=&NomeBiblDim..&TabDim data=WORK.SCDU
force;
run;

%goto Saida;


%Saida:

/* Inserção Tipo I  e II */
/* ********************* */
proc append base=&NomeBiblDim..&TabDim data=WORK.SCDI
force;
run;

/* Ordenação Geral */
/* *************** */
%if &TipoChv = 'S' %then %do;
proc sort data=&NomeBiblDim..&TabDim;
    by &CmpChaveSkDim;
run;
%end;

proc sql;
DROP TABLE WORK.TamStg;
DROP TABLE WORK.TamDim;
DROP TABLE WORK.ChaveStg;
DROP TABLE WORK.ChaveDim;
DROP TABLE Work.SCDI;
DROP TABLE Work.SCDU;
DROP TABLE Work.SCDData;
quit;
%mend;

Sem comentários:

Publicar um comentário