If statement in SAS macro not always working-CodePudding

I'm relatively new to SAS coding, and I'm trying to write this (see the whole code down below) macro that should evaluate performances (to_worse, to_better, recoveded, etc.) of several teams.

The problem is that this rule here:

        if team_&y.="&PROVIDER." and team_&i. eq "" and balance_&i. = 0 then do; regolarizzato = "regolarizzato"; end;
        else if team_&i. ne team_&y. and team_&y. eq "&PROVIDER." and team_&i. ne "" then do; to_worse="to_worse"; end;
        else if team_&i. = team_&y. and team_&y. eq "&PROVIDER." and balance_&y. > balance_&i. then do; to_better = "to_better"; end;

does not seem to be always properly wowking; it works most of the times, but not always, and I don't understand the reason why it's failing sometimes. Could any kindheardted fella please explain why this happens? Thank you so much in advance!

%let oggi = '07oct2022'd;


%let mese = %sysfunc (MONTH(&oggi.));
%IF &mese. < 10 %THEN %do; %let mese = 0&mese.; %end;
%let giorno = %sysfunc (DAY(&oggi.));
%let anno = %sysfunc(Year(&oggi.));
/*%IF &giorno. < 10 %THEN %do; %let giorno = 0&giorno.; %end;*/



%macro COSTI_1;

%let i = 0;
%DO i = 0 %TO &giorno.; 

    data COSTI_&i.;
    set data.initial_db_&mese.;
    format balance_&i. commax14.2;
    keep contract_number team_&i. balance_&i.;
    run;

%end;

%mend;
%COSTI_1;

data COSTI_db;
set COSTI_0;
run;


%macro COSTI_2;

%let i = 1;
%DO i = 1 %TO &giorno.;

    PROC SQL;
        CREATE TABLE COSTI_db AS
        SELECT         
             A.*,
             B.*
        FROM COSTI_db AS A LEFT JOIN COSTI_&i. AS B 
            ON (A.contract_number = B.contract_number);
        QUIT;
    run;
%end;

%mend;
%COSTI_2;


data COSTI_db;
set COSTI_db;
length team $ 20;
format team $CHAR30.;
team="altro";
run;



%MACRO COSTI_PROVIDER (PROVIDER);

data COSTI_db_&Provider.;
set COSTI_db;
run;
    
    %macro COSTI_A;
    
    %let i = 0;
    
    %DO i = 0 %TO &giorno.;
    
        data COSTI_db_&Provider.;
        set COSTI_db_&Provider.;
        
            if team_&i. = "&PROVIDER." then team = "&PROVIDER.";
            
        run;
        
    %end;   
    
    %mend;
    %COSTI_A;
    
    
    DATA COSTI_&PROVIDER.;
    set COSTI_db_&Provider. (where =(team="&PROVIDER."));
    
    length to_worse $ 20;
    format to_worse $CHAR30.;
    length to_better $ 20;
    format to_better $CHAR30.;
    length regolarizzato $ 20;
    format regolarizzato $CHAR30.;
    
    to_worse="no";
    to_better="no";
    regolarizzato="no";
    
    run;
    
    
    %macro to_worse;
    
    %let i = 1;
    %let y = %eval(&i.-1);
    
    %DO i = 1 %TO &giorno.;
    
        data COSTI_&PROVIDER.;
        set COSTI_&PROVIDER.;
            
            if team_&y.="&PROVIDER." and team_&i. eq "" and balance_&i. = 0 then do; regolarizzato = "regolarizzato"; end;
            else if team_&i. ne team_&y. and team_&y. eq "&PROVIDER." and team_&i. ne "" then do; to_worse="to_worse"; end;
            else if team_&i. = team_&y. and team_&y. eq "&PROVIDER." and balance_&y. > balance_&i. then do; to_better = "to_better"; end;
    
        run;
    
    %end;
    
    %mend;
    %to_worse;
    
    
    data COSTI_&PROVIDER.;
    set COSTI_&PROVIDER.;
    
    length esito_finale $ 20;
    format esito_finale $CHAR30.;
    format balance_affido commax12.2;
        
        if to_worse="to_worse" then esito_finale="to_worse";
        else if regolarizzato = "regolarizzato" then esito_finale="regolarizzato";
        else if to_better = "to_better" then esito_finale = "to_better";
        else if team_&giorno. = "&PROVIDER." then esito_finale = "in_gestione_oggi";
        
        if richiamo_o_repo = "&PROVIDER." and inflows < -1 then esito_finale = "richiamo";
        if richiamo_o_repo = "&PROVIDER." and to_normal > 1 then esito_finale = "repo";
    
        if team_0 = "&PROVIDER." then balance_affido = balance_0;
        else balance_affido = -1;
    
        drop INFLOWS TO_NORMAL RICHIAMO_O_REPO;
        
    run;
    
    
    %macro COSTI_B;
    
    %let i = 0;
    
    %DO i = 1 %TO &giorno.;
    
    data COSTI_&PROVIDER.;
    set COSTI_&PROVIDER.;
        
        if team_&i. = "&PROVIDER." and balance_affido = -1 then balance_affido=balance_&i.;
        
    run;
    %end;
    
    %mend;
    %COSTI_B;
    
    
    
    proc sql;
        create table RIEPILOGO_&PROVIDER.
        as select esito_finale, sum(balance_affido) as somma_balance_affido
        from COSTI_&PROVIDER.
        group by esito_finale;
        quit;
    
    
    data RIEPILOGO_&PROVIDER.;
    set RIEPILOGO_&PROVIDER.;
    format somma_balance_affido commax12.2;
    run;
    
   

%MEND;


%COSTI_PROVIDER(TEAM_A);
%COSTI_PROVIDER(TEAM_B);
%COSTI_PROVIDER(TEAM_C);
%COSTI_PROVIDER(TEAM_D);

CodePudding user response：

The macro that is generating that IF statement seems to have few issues.

First the macro variable Y is always going to be 0 since it is set to %eval(1-1). Did you intend Y to always be one less than I? If so move that %LET statement inside the %DO loop.

Second you keep reading and writing the same dataset over and over. You should probably move the %DO loop so that it can all be done with one data step.

%macro to_worse;
%local i y ;
data COSTI_&PROVIDER.;
  set COSTI_&PROVIDER.;
%do i = 1 %TO &giorno.;
  %let y = %eval(&i.-1);
  if      team_&i. eq ""       and team_&y. eq "&PROVIDER." and balance_&i. = 0 then regolarizzato = "regolarizzato"; 
  else if team_&i. ne team_&y. and team_&y. eq "&PROVIDER." and team_&i. ne "" then to_worse="to_worse";
  else if team_&i. eq team_&y. and team_&y. eq "&PROVIDER." and balance_&y. > balance_&i. then to_better = "to_better"; 
%end;
run;
%mend to_worse;

And finally your set of IF/THEN/ELSE conditions do not cover all of the possible combinations of values of TEAM_&I, TEAM_&Y, BALANCE_&I and BALANCE_&Y. Are you sure that the situations where you think it is not doing what you want are not just the situations that fall into one of those uncovered combinations?