quinta-feira, 13 de setembro de 2012

SAS Macro para tarefa de Lookup


%let APPDIR=%SYSGET(FOLDER_NAME);
%include "&APPDIR\PROG\GECRPDeclaraLibNames.sas";
/* Atribuição de bibliotecas (Substitua o código acima com as suas bibliotecas) */

%macro GELookup(NomeBiblOut, TabOut, NomeBiblMan, TabMan, CmpChaveMan, CmpRetornoMan, NomeBiblPes, TabPes, CmpChavePes, CmpRetornoPes);
/*
    PROGRAMA SAS
         PROJ: Tarefas gerais de ETL.
         DESC: Realiza a tarefa de Lookup tendo uma tabela Mandatória e uma tabela de Pesquisa.
         AUTOR: Erick Furst erick.furst@coresynesis.com.br
         DATA: 2009-08-27
     INPUTS/OUTPUTS
        INs : Nome biblioteca e nome tabela de saída (output): NomeBiblOut, TabOut.
 Nome biblioteca, nome tabela de mandatória, campo(s) chave da tabela mandatória e campo(s)
de retorno da tabela mandatória: NomeBiblMan, TabMan, CmpChaveMan, CmpRetornoMan.
 Nome biblioteca, tabela de pesquisa, campo(s) chave da tabela pesquisa e campo(s) de retorno da
tabela pesquisa: NomeBiblPes, TabPes, CmpChavePes, CmpRetornoPes.
        OUTs: Tabela de saída (output).
    MODIFICACOES
        2009-09-08 erick.furst@coresynesis.com.br
             (+) Inclusão de chamada de Libs por variável de sistema.
*/

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

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

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

CmpChaveMan = &CmpChaveMan;
CmpChaveManTra = CmpChaveMan;
Pos = 1;
PosIni = 1;
PosFim = length(CmpChaveManTra);
OrdemMan = 0;

do while (PosIni < PosFim AND Pos ^= 0);

Pos = INDEX(CmpChaveManTra, ",");
CmpChaveManLst = SUBSTR(CmpChaveManTra, PosIni, Pos - 1);
CmpChaveManTra = SUBSTR(CmpChaveManTra, Pos + 1, PosFim - Pos);
OrdemMan = OrdemMan + 1;
output;

drop CmpChaveManTra; drop Pos; drop PosIni; drop PosFim;
end;

run;


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

CmpChavePes = &CmpChavePes;
CmpChavePesTra = CmpChavePes;
Pos = 1;
PosIni = 1;
PosFim = length(CmpChavePesTra);
OrdemPes = 0;

do while (PosIni < PosFim AND Pos ^= 0);

Pos = INDEX(CmpChavePesTra, ",");
CmpChavePesLst = SUBSTR(CmpChavePesTra, PosIni, Pos - 1);
CmpChavePesTra = SUBSTR(CmpChavePesTra, Pos + 1, PosFim - Pos);
OrdemPes = OrdemPes + 1;
output;

drop CmpChavePesTra; drop Pos; drop PosIni; drop PosFim;
end;

run;

/* DATA STEP que gera o código de lookup para a função CALL EXECUTE. */
data _null_;

/* Entradas do Lookup */
/* ****************** */
* Informar Tabela de saída;
NomeBiblOut = &NomeBiblOut;
TabOut = &TabOut;
* Informar Tabela de mandatória, campo(s) chave da tabela mandatória e campo(s) de retorno da tabela mandatória;
NomeBiblMan = &NomeBiblMan;
TabMan = &TabMan;
CmpChaveMan = &CmpChaveMan;
CmpRetornoMan = &CmpRetornoMan;
* Informar Tabela de pesquisa, campo(s) chave da tabela pesquisa e campo(s) de retorno da tabela pesquisa;
NomeBiblPes = &NomeBiblPes;
TabPes = &TabPes;
CmpChavePes = &CmpChavePes;
CmpRetornoPes = &CmpRetornoPes;


length TxtCodLookup $5000;
length TxtCodLookup2 $1000;
length TxtCodLookup3 $1000;
length TxtCodLookup4 $1000;


/* 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 '"",""'");

* Chave da Tabela Mandatória;
CmpChaveManCon = &CmpChaveMan || '                                                                                                                                                                     ';
call rxchange(rx,length(CmpChaveManCon),CmpChaveManCon); /* Substituição de valor */
CmpChaveManCon=compress(CmpChaveManCon); /* Eliminação de espaços brancos. */

* Campos de retorno da Tabela Mandatória;
CmpRetornoManCon = &CmpRetornoMan || '                                                                                                                                                                     ';
call rxchange(rx,length(CmpRetornoManCon),CmpRetornoManCon); /* Substituição de valor */
CmpRetornoManCon=compress(CmpRetornoManCon); /* Eliminação de espaços brancos. */

* Chave da Tabela de Pesquisa;
CmpChavePesCon = &CmpChavePes || '                                                                                                                                                                     ';
call rxchange(rx,length(CmpChavePesCon),CmpChavePesCon); /* Substituição de valor */
CmpChavePesCon=compress(CmpChavePesCon); /* Eliminação de espaços brancos. */

* Campos de retorno da Tabela de Pesquisa;
CmpRetornoPesCon = CmpRetornoPes || '                                                                                                                                                                     ';
call rxchange(rx,length(CmpRetornoPesCon),CmpRetornoPesCon); /* Substituição de valor */
CmpRetornoPesCon=compress(CmpRetornoPesCon); /* Eliminação de espaços brancos. */


/* Monta String para execução do código de lookup na função CALL EXECUTE. */
TxtCodLookup = 'DATA ' || &NomeBiblOut || '.' || &TabOut || '; ';

/* O cursor abaixo monta o comando lenght para a tabela mandatória.*/
length TamCol $1000;
length TxtCodLkMan $5000;

declare hash LstTamColMan(dataset:"WORK.TamMan");

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

declare hiter iterobj1('LstTamColMan');

crTamCol = iterobj1.first();

do while (crTamCol=0);

TxtCodLkMan = CATS(TxtCodLkMan, TamCol);

crTamCol = iterobj1.next();

end;

drop TamCol; drop crTamCol;

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

TxtCodLookup2 =
' declare hash TabMandatoria(dataset:"' || &NomeBiblMan || '.' || &TabMan || '"); ' ||
'TabMandatoria.defineKey("' || TRIM(CmpChaveManCon) || '"); ' ||
'TabMandatoria.defineData("' || TRIM(CmpRetornoManCon) || '"); ' ||
'TabMandatoria.defineDone(); ';

TxtCodLookup = CATS(TxtCodLookup, TxtCodLookup2);


/* O cursor abaixo monta o comando lenght para a tabela de pesquisa.*/
length TamCol $1000;
length TxtCodLkPes $5000;

declare hash LstTamColPes(dataset:"WORK.TamPes");

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

declare hiter iterobj2('LstTamColPes');

crTamCol = iterobj2.first();

do while (crTamCol=0);

TxtCodLkPes = CATS(TxtCodLkPes, TamCol);

crTamCol = iterobj2.next();

end;

drop TamCol; drop crTamCol;

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

TxtCodLookup3 =
'declare hash TabPesquisa(dataset:"' || &NomeBiblPes || '.' || &TabPes || '"); ' ||
'TabPesquisa.defineKey("' || TRIM(CmpChavePesCon) || '"); ' ||
'TabPesquisa.defineData("' || TRIM(CmpRetornoPesCon) || '"); ' ||
'TabPesquisa.defineDone(); ' ||
'declare hiter iterobj(''TabMandatoria''); ' ||
'rc = iterobj.first(); ' ||
'do while (rc=0); ';

TxtCodLookup = CATS(TxtCodLookup, TxtCodLookup3);


/* O cursor abaixo monta o comando de comparação entre o(s) campo(s) chave da tabela de pesquisa com o(s)
campo(s) chave da tabela mandatória. Para a posterior recuperação de campos feito pelo comando FIND do objeto HASH.
Ex.: CampoChaveTabelaPesquisa = CampoChaveTabelaMandatoria; */
length OrdemMan 3; length CmpChaveManLst $32;

declare hash LstMan(dataset:"WORK.ChaveMan");

LstMan.defineKey("OrdemMan");
LstMan.defineData("OrdemMan","CmpChaveManLst");
LstMan.defineDone();

declare hiter iterobj3('LstMan');

length OrdemPes 3; length CmpChavePesLst $32;

declare hash LstPes(dataset:"WORK.ChavePes");

LstPes.defineKey("OrdemPes");
LstPes.defineData("OrdemPes","CmpChavePesLst");
LstPes.defineDone();

cursor = iterobj3.first();

do while (cursor=0);

OrdemPes = OrdemMan;
cursor = LstPes.find();
TxtCodLookup = CATS(TxtCodLookup, CmpChavePesLst || ' = ' || CmpChaveManLst || '; ');

cursor = iterobj3.next();

end;

drop CmpChavePesLst; drop CmpChaveManLst; drop OrdemPes; drop OrdemMan; drop cursor;

/* Concatenação dos blocos da string na variável final TxtCodLookup. */
TxtCodLookup4 =
' rc = TabPesquisa.find(); ' ||
' output; ' ||
' rc = iterobj.next(); ' ||
'end; ' ||
'drop rc; ' ||
'run; ';

TxtCodLookup = CATS(TxtCodLookup, TxtCodLookup4);

/* Gera e executa o código de lookup. */
CALL EXECUTE (TxtCodLookup);

drop _All_;

run;

%mend;


/* ----------------------------------------------------  */

/* Como usar */


%let APPDIR=%SYSGET(FOLDER_NAME);
%include "&APPDIR\PROG\GECRPDeclaraLibNames.sas";

/* Atribuição de bibliotecas (Substitua o código acima com as suas bibliotecas) */


%include "&APPDIR\PROG\GELookup.sas";
/* Arquivo com o código da macro %macro GELookup */

/* Entradas do Lookup, separar campos por vírgulas ( , ).*/
/* ***************************************************** */
* Informar Tabela de saída;
%let NomeBiblOut = 'LIBRARY_NAME';
%let TabOut = 'ResultadoLookup';  /* Nome da tabela de output */
/*-------------------------------------------------------*/
* Informar Tabela de mandatória, campo(s) chave da tabela mandatória e campo(s) de retorno da tabela mandatória;
%let NomeBiblMan = 'LIBRARY_NAME';
%let TabMan = 'MANDATORY_TABLE';
%let CmpChaveMan = 'KEY_COLUMN';
%let CmpRetornoMan = 'KEY_COLUMN';
/*-------------------------------------------------------*/
* Informar Tabela de pesquisa, campo(s) chave da tabela pesquisa e campo(s) de retorno da tabela pesquisa;
%let NomeBiblPes = 'LIBRARY_NAME';
%let TabPes = 'LOOKUP_TABLE';
%let CmpChavePes = 'KEY_COLUMN';
%let CmpRetornoPes = 'RETURNED_COLUMN_1,RETURNED_COLUMN_2';
/*-------------------------------------------------------*/

%GELookup(&NomeBiblOut, &TabOut, &NomeBiblMan, &TabMan, &CmpChaveMan, &CmpRetornoMan,
 &NomeBiblPes, &TabPes, &CmpChavePes, &CmpRetornoPes);


Sem comentários:

Publicar um comentário