Using variable to update a column in a macro

[ph10]

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;

[ballardw]

---

@ph10 wrote:

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:

Help us help you. WHICH variable needs to be updated? Where in the code, data or output? Be very specific.

What is the value to be set to? The value of a variable or the name of a variable or what?

I guess randomly at any of the 100 or so lines of code and make a suggestion but without some information as to what is going wrong then we can't help.

First request: Post an example of the entire process that runs without any macro variables at all. Best would be to provide some example data in the form of a data step as well.

Then, with that code that works, explain what is not working with your macro.

Another hint: One of the basic tools for debugging macro code behavior is to set OPTIONS MPRINT; Then you can see the code generated and have a better chance of determining where fixes are needed.

[ph10]

Sorry for the mess. Here is the code, that i need help. Basicly, i have a macro with the args, in that case the first word, for example,

in %VLD00(DT_OCR_FATO, 'NUMB'), i want to use the DT_OCR_FATO.

I have to put this arguement in a update sql. 

The variable that a i want to use on the update sql is called COLUNA (column), that is bolded at example below.;
QUIT;

%MACRO VLD00(CAMPO, TIP);

/*** DECLARAÇÃO DAS VARIÁVEIS ****************/

%GLOBAL COLUNA;
%LET COLUNA = &CAMPO;
%let dt_prct = &sysdate9;
%put &CAMPO;
%put &COLUNA;
%put &tip;

/*** INSERE DADOS DE REGISTROS COM ERRO NA TABELA RESUMO ****************/
PROC SQL STIMER ;
INSERT INTO WORK.RESUMO_TMP (DT_PRCT, COLUNA, QTDE, VL_EVT)
SELECT &DT_PRCT, COLUNA, SUM(QTDE), SUM(VL_EVT)
FROM
WORK.PROD
WHERE
ind_&CAMPO = 'F'
GROUP BY
&DT_PRCT, SG_SIS_OGM_EVT, CD_TRNG, CD_OCR, CD_EVT;
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');

[Kurt_Bremser]

You create a macro variable coluna, which you never use.

Or did you intend to use it here:

INSERT INTO WORK.RESUMO_TMP (DT_PRCT, COLUNA, QTDE, VL_EVT)

?

If yes, you need to use the & macro trigger so that the macro processor can resolve it:

INSERT INTO WORK.RESUMO_TMP (DT_PRCT, &COLUNA., QTDE, VL_EVT)

Do yourself (and others) a favor and stop shouting at SAS. Lowercase code is much easier on the eyes.

[Tom]

Do you want to put the string NR_BLH into the variable COLUNA?

So in normal SAS code you want to run the assignment statement:

coluna = "NR_BLH";

To use the macro variable instead of the literal text just replace NR_BLH with a reference to the macro variable, which in your macro is named CAMPO.

"&campo."

But later on you are also trying to use CAMPO (or really the string NR_BLH or DT_OCR_FATO) as part of the name of a variable:

WHERE ind_&CAMPO = 'F'

So do you have variables named IND_NR_BLH and COLUNA ?

[Tom · Accepted solution]

What did you think this statement was going to do?

COLUNA1=CALL SYMPUT(VVALUE(COLUNA));

At first it looks like an assignment statement to change the value of a variable called COLUNA1.

But then on the right side it does not have a value.  

If you are trying to do an assignment statement then remove the reference to CALL SYMPUT().

COLUNA1=VVALUE(COLUNA);

Perhaps you are trying to use the CALL SYMPUT() function (statement) ?

Then two things are wrong.

First it is almost always wrong to use CALL SYMPUT().  You should use CALL SYMPUTX() instead. The only time you should use CALL SYMPUT() is when you need to create macro variables with leading or trailing spaces in them. Which is ,in general, never needed.

The syntax for call symputx() would be more like:

call symputx('COLUNA1',VVALUE(COLUNA));

The first argument is a string that is used as the NAME of the macro variable to create.  The second argument is the value to put into the macro variable.  If you use a numeric expression there then CALL SYMPUTX() will convert it into a string.  Leading and trailing spaces are removed from the second argument and so do not make it into the macro variable.