BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
erickdt
Obsidian | Level 7

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? 😞

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

3 REPLIES 3
Reeza
Super User

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;

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p0pbehl7wj5sl4n1ov1ortnehtba.htm&docse...

 


@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? 😞


 

art297
Opal | Level 21

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

 

erickdt
Obsidian | Level 7

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!!!! 😄 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 592 views
  • 0 likes
  • 3 in conversation