topic Re: Macro in proc report in SAS Programming

Macro in proc report

snip — Wed, 29 Nov 2023 21:07:13 GMT

Hello,

I'm coming back to you because I'm stuck.

I want to use a macro variable to feed my compute columns (top_tx_min_cible_&var) but I think I am using the macro incorrectly (the order of my instructions may be wrong?!!)

I have a 50 computed column hence my need for a macro variable.

I don't want to use 50 compute blocks like this:

For information, I used this treatment without the macro to test on a few columns and it works.

compute top_tx_min_cible_A;
top_tx_min_cible_A = tx_min_cible_A.sum;
endcomp;

compute top_tx_max_cible_A;
        top_tx_max_cible_A= tx_max_cible_A.sum;
    endcomp;

    compute top_tx_min_temoin_A;
        top_tx_min_temoin_A = tx_min_temoin_A.sum;
    endcomp;

    compute top_tx_max_temoin_A;
        top_tx_max_temoin_A = tx_max_temoin_A.sum;
    endcomp;

%macro calc_top_sum(var);

    compute top_tx_min_cible_&var;
        top_tx_min_cible_&var = tx_min_cible_&var.sum;
    endcomp;


    compute top_tx_max_cible_&var;
        top_tx_max_cible_&var = tx_max_cible_&var.sum;
    endcomp;


    compute top_tx_min_temoin_&var;
        top_tx_min_temoin_&var = tx_min_temoin_&var.sum;
    endcomp;

   
    compute top_tx_max_temoin_&var;
        top_tx_max_temoin_&var = tx_max_temoin_&var.sum;
    endcomp;
%mend calc_top_sum;

PROC REPORT ;
COLUMN …….

define  tx_min_cible_A /analysis ;
define  tx_max_cible_A /analysis ;
define  tx_min_temoin_A /analysis ;
define  tx_max_temoin_A/analysis ;

define  TOP_tx_max_temoin_A   /computed ;
define  TOP_tx_min_temoin_A  /computed ;
define  TOP_tx_max_cible_A  /computed ;
define  TOP_tx_min_cible_A /computed ;

define  tx_min_cible_B /analysis ;
define  tx_max_cible_B /analysis ;
define  tx_min_temoin_B /analysis ;
define  tx_max_temoin_B/analysis ;

define  TOP_tx_max_temoin_B   /computed ;
define  TOP_tx_min_temoin_B  /computed ;
define  TOP_tx_max_cible_B  /computed ;
define  TOP_tx_min_cible_B /computed ;


define  tx_min_cible_C /analysis ;
define  tx_max_cible_C /analysis ;
define  tx_min_temoin_C /analysis ;
define  tx_max_temoin_C/analysis ;

define  TOP_tx_max_temoin_C   /computed ;
define  TOP_tx_min_temoin_C  /computed ;
define  TOP_tx_max_cible_C  /computed ;
define  TOP_tx_min_cible_C /computed ;
.
.
.
.
%calc_top_sum(A); 
%calc_top_sum(B); 
%calc_top_sum(C); 
.
.
.
.
BREAK AFTER……
RUN ;

Thank's

Re: Macro in proc report

PaigeMiller — Wed, 29 Nov 2023 22:09:48 GMT

This has an error

        top_tx_min_cible_&var = tx_min_cible_&var.sum;

It should say

        top_tx_min_cible_&var = tx_min_cible_&var..sum;

Note there must be two dots after &var on the right hand side of the equal sign, one dot to end the name of the macro variable, and one dot to indicate the dot that PROC REPORT requires in this situation before the word SUM. You need to fix this throughout your code.

From now on when you get an error in code, you MUST (it is not optional) show us the entire log for this PROC, every single line in the log for this PROC, not just the error messages. If the above still doesn't fix your problem, we need to see the ENTIRE log for this PROC, not just the error messages. In addition, you should turn on macro debugging tools by running this line of code before you run the macro:

options mprint;

Re: Macro in proc report

Cynthia_sas — Wed, 29 Nov 2023 22:19:24 GMT

Hi:
In addition, you do NOT show your entire COLUMN statement. Do you use any ACROSS variables? Are any of your ANALYSIS or COMPUTED items under an ACROSS variable? The only scenario that will work with the statements as you show them is if there are NOT any ACROSS variables used with these ANALYSIS or COMPUTED items.
Cynthia

Re: Macro in proc report

snip — Thu, 30 Nov 2023 21:29:16 GMT

I have the same problem with that... with error ERROR: Invalid column specification in CALL DEFINE. I can't find the error

 /*SIGN_&var ===> display AND gaint_&var IS ANALYSIS */
%macro apply_style_sign(var);
 compute SIGN_&var; 

   if SIGN_&var=1 and gaint_&var..sum >0 then do; 
        call define( 'gaint_&var.sum' , "style", "style=[background=#C4D79B]" );
    end;

    else if SIGN_&var=1 and gaint_&var..sum <=0 then do;   
        call define( 'gaint_&var.sum' , "style", "style=[background=#FABF8F]" );
    end;
endcomp;
%mend apply_style_sign;

Re: Macro in proc report

Cynthia_sas — Fri, 01 Dec 2023 02:21:02 GMT

Hi:
Just a thought. Macro variable references cannot be resolved in single quotes. So every place that you reference 'gaint_&var..sum' you will need to change the code to "&gaint_&var..sum" or otherwise, PROC REPORT will not be able to get a valid variable name at resolution time.
Cynthia