Hello, I have a job to do that i want to use a macro. The job's purpose is to identify rows with probems in a dataset. To do that, i have a macro with the columns to be avaliated. The final task is to create a summary table, with the columns and the amount of errors. My problem is that i have a variable with the column name and i having ptoblems to update the summary table with the variable's value. Here is my job: %MACRO VLD00(CAMPO, TIP); /*** DECLARAÇÃO DAS VARIÁVEIS ****************/ %GLOBAL COLUNA COLUNA1; %LET COLUNA = &CAMPO; %let dt_prct = &sysdate9; %LET TTL = 0; %put &CAMPO; %put &COLUNA; %put &tip; DATA _NULL_; call symput("dt_prct", COMPRESS("'" || PUT(day("&dt_prct"d),Z2.) || '.' || PUT(month("&dt_prct"d),Z2.) || '.' || PUT(Year("&dt_prct"d), Z4.) || "'")); RUN; data _NULL_; LENGTH COLUNA1 VARCHAR(60); ------------------------------------------------------------ VARIABLE WITH PROBLEM COLUNA1=CALL SYMPUT(VVALUE(COLUNA)); RUN; --------------------------------------------------------- /*** VARIÁVEIS DO SQL -> VARIA DE ACORDO COM O TIPO DE DADO ****************/ %IF &TIP = 'NUMB' %THEN %DO; %put "foi na primeira"; %LET SQL = (CASE WHEN A.&CAMPO IS NULL THEN 'F' WHEN A.&CAMPO = 0 THEN 'F' ELSE 'T' END); %LET DTSET = &CAMPO IS NULL OR &CAMPO = 0; %put &sql; %END; %ELSE %IF &TIP = 'TEXT' %THEN %DO; %put "foi de segunda"; %LET SQL = (CASE WHEN A.&CAMPO IS NULL THEN 'F' WHEN A.&CAMPO = '.' THEN 'F' ELSE 'T' END); %LET DTSET = &CAMPO IS NULL OR &CAMPO = '.'; %put &sql; %END; /*** CRIA TABELA BASE COM AS AVALIAÇÕES DA QUALIDADE DOS DADOS ****************/ PROC SQL; CREATE TABLE PROD_&CAMPO AS SELECT A.DT_MVT, A.DT_BAL, A.SG_SIS_OGM_EVT, A.CD_TRNG, A.CD_OCR, A.CD_EVT, &SQL AS IND_&CAMPO, COUNT(A.NR_CTR) AS QTDE, SUM(A.VL_EVT) AS VL_EVT FROM WORK.BASE A GROUP BY A.DT_MVT, A.DT_BAL, A.SG_SIS_OGM_EVT, A.CD_TRNG, A.CD_OCR, A.CD_EVT, IND_&CAMPO ORDER BY A.DT_MVT, A.DT_BAL, A.SG_SIS_OGM_EVT, A.CD_TRNG, A.CD_OCR, A.CD_EVT, IND_&CAMPO ; QUIT; /*** CRIA TABELA BASE SOMENTE COM OS DADOS DO CAMPO COM ERRO ****************/ DATA WORK.PROD_&CAMPO.1; SET WORK.PROD_&CAMPO; WHERE ind_&CAMPO = 'F' ; RUN; /*** CRIA TABELA SUMARIZADA SOMENTE COM OS DADOS DO CAMPO COM ERRO ****************/ PROC SQL; CREATE TABLE WORK.PROD_&CAMPO.2 AS SELECT COUNT(*) AS TTL_REG FROM WORK.PROD_&CAMPO.1 ; QUIT; /*** CRIA O CAMPO TTL COM O TOTAL FORMATADO ****************/ DATA _NULL_; SET WORK.PROD_&CAMPO.2; CALL SYMPUT("TTL", PUT(TTL_REG, 20.)); ; RUN; /*** INSERE DADOS DE REGISTROS COM ERRO NA TABELA RESUMO ****************/ PROC SQL STIMER ; INSERT INTO WORK.RESUMO_TMP (DT_PRCT, SG_SIS_OGM_EVT, CD_TRNG, CD_OCR, CD_EVT, QTDE, VL_EVT) SELECT &DT_PRCT, SG_SIS_OGM_EVT, CD_TRNG, CD_OCR, CD_EVT, SUM(QTDE), SUM(VL_EVT) FROM WORK.PROD_&CAMPO.1 WHERE ind_&CAMPO = 'F' GROUP BY &DT_PRCT, SG_SIS_OGM_EVT, CD_TRNG, CD_OCR, CD_EVT; QUIT; PROC SQL; UPDATE WORK.RESUMO_TMP SET dt_prct = &dt_prct; quit; UPDATE NOT WORKING ------------------------------------------------------------------------------------------------------------- PROC SQL; UPDATE WORK.RESUMO_TMP SET COLUNA = &COLUNA1; quit; %MEND VLD00; %VLD00(NR_BLH, 'TEXT'); %VLD00(DT_OCR_FATO, 'NUMB'); %VLD00(CD_IPMC_ITCE, 'NUMB'); %VLD00(CD_ITCE_CNL_ATDT, 'NUMB'); %VLD00(CD_IDFR_SSS_ATDT, 'TEXT'); %VLD00(CD_TITR_OPR, 'NUMB'); PROC SQL ; CREATE TABLE WORK.RESUMO AS SELECT DT_PRCT, SG_SIS_OGM_EVT, COLUNA, CD_TRNG, CD_OCR, CD_EVT, MAX(QTDE) AS QTDE, MAX(VL_EVT) AS VL_EVT FROM WORK.RESUMO_TMP GROUP BY DT_PRCT, SG_SIS_OGM_EVT, COLUNA, CD_TRNG, CD_OCR, CD_EVT ORDER BY DT_PRCT, SG_SIS_OGM_EVT, COLUNA, CD_TRNG, CD_OCR, CD_EVT ; QUIT;
... View more