I'm trying appling this macro:
%MACRO EXPOSICOES(TABELA,VL_PMT,EXPOSICAO,VAR_PROVISAO,CONDICAO);
PROC SQL;
CREATE TABLE SALDO_&TABELA. AS
SELECT CPFCNPJ,
%IF &TABELA NOT IN (OUTROS,AGRO,CHEQUE) %THEN %DO;
SUM(&VL_PMT.) AS PMT_&TABELA.,
%END;
MAX(DIAS_ATRASO) AS DIAS_ATRASO_&TABELA.,
FROM TABELA WHERE CPFCNPJ > 0
GROUP BY CPFCNPJ
;QUIT;
%MEND EXPOSICOES;
%EXPOSICOES(OUTROS,.,TABELA,VAR_PROVISAO)
however i always get this message error:
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&TABELA NOT IN (OUTROS,AGRO,CHEQUE)
ERROR: The macro EXPOSICOES will stop executing.
anyone could help me? 😞
You have a couple of problems with your macro. Like @Reeza mentioned, you'd have to use the minoperator option to us the in operator in a macro. Plus, you would have to change the way you use the operator.
Plus you have an extra comma in your proc sql code:
MAX(DIAS_ATRASO) AS DIAS_ATRASO_&TABELA.,
And what are you trying to accomplish with the sum function?
Finally, you declare a fifth parameter in your macro, but only include four values when you call the macro.
Here is the code I ran:
data tabela;
input cpfcnpj dias_atraso;
cards;
1 1
1 2
1 3
1 4
2 2
2 4
2 6
2 8
;
run;
options minoperator;
%MACRO EXPOSICOES(TABELA,VL_PMT,EXPOSICAO,VAR_PROVISAO,CONDICAO);
PROC SQL;
CREATE TABLE SALDO_&TABELA. AS
SELECT CPFCNPJ,
%IF NOT (&TABELA IN OUTROS AGRO CHEQUE) %THEN %DO;
SUM(&VL_PMT.) AS PMT_&TABELA.,
%END;
MAX(DIAS_ATRASO) AS DIAS_ATRASO_&TABELA. /*,*/
FROM TABELA WHERE CPFCNPJ > 0
GROUP BY CPFCNPJ
;
QUIT;
%MEND EXPOSICOES;
%EXPOSICOES(OUTROS,.,TABELA,VAR_PROVISAO)
Art, CEO, AnalystFinder.com
I suspect its the NOT IN that's causing you issues, this is a known common issue if you search for it.
I would recommend switching to multiple AND or look into MINOPERATOR.
%IF &TABELA !=OUTROS and &TABELA != AGRO and &TABELA != CHEQUE %THEN %DO;
@erickdt wrote:
I'm trying appling this macro:
%MACRO EXPOSICOES(TABELA,VL_PMT,EXPOSICAO,VAR_PROVISAO,CONDICAO);
PROC SQL;
CREATE TABLE SALDO_&TABELA. AS
SELECT CPFCNPJ,
%IF &TABELA NOT IN (OUTROS,AGRO,CHEQUE) %THEN %DO;
SUM(&VL_PMT.) AS PMT_&TABELA.,
%END;
MAX(DIAS_ATRASO) AS DIAS_ATRASO_&TABELA.,
FROM TABELA WHERE CPFCNPJ > 0
GROUP BY CPFCNPJ
;QUIT;
%MEND EXPOSICOES;
%EXPOSICOES(OUTROS,.,TABELA,VAR_PROVISAO)
however i always get this message error:
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&TABELA NOT IN (OUTROS,AGRO,CHEQUE)
ERROR: The macro EXPOSICOES will stop executing.
anyone could help me? 😞
You have a couple of problems with your macro. Like @Reeza mentioned, you'd have to use the minoperator option to us the in operator in a macro. Plus, you would have to change the way you use the operator.
Plus you have an extra comma in your proc sql code:
MAX(DIAS_ATRASO) AS DIAS_ATRASO_&TABELA.,
And what are you trying to accomplish with the sum function?
Finally, you declare a fifth parameter in your macro, but only include four values when you call the macro.
Here is the code I ran:
data tabela;
input cpfcnpj dias_atraso;
cards;
1 1
1 2
1 3
1 4
2 2
2 4
2 6
2 8
;
run;
options minoperator;
%MACRO EXPOSICOES(TABELA,VL_PMT,EXPOSICAO,VAR_PROVISAO,CONDICAO);
PROC SQL;
CREATE TABLE SALDO_&TABELA. AS
SELECT CPFCNPJ,
%IF NOT (&TABELA IN OUTROS AGRO CHEQUE) %THEN %DO;
SUM(&VL_PMT.) AS PMT_&TABELA.,
%END;
MAX(DIAS_ATRASO) AS DIAS_ATRASO_&TABELA. /*,*/
FROM TABELA WHERE CPFCNPJ > 0
GROUP BY CPFCNPJ
;
QUIT;
%MEND EXPOSICOES;
%EXPOSICOES(OUTROS,.,TABELA,VAR_PROVISAO)
Art, CEO, AnalystFinder.com
art297, thx so much, it's working now...
answering your questions:
1 - I haven't put my complete query here, i need make this query more than 5 times, so the max function will work for another "product" 🙂
2 - as mentioned "condicao" is a condition from another products...
Thanks for the fast repliyng!!!! 😄
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.