Using max function in macro SAS

Question

I'm trying to find the max of four variables, Value_A Value_B Value_C Value_D, within a macro. I thought I could do %sysfunc(max(value_&i.)) but that isn't working. My full code is:

%let i = (A B C D);
%macro maxvalue;

    data want;
    set have;
        %do j = 1 %to %sysfunc(countw(&list.));
        %let i = %scan(&list.,&j.);
            value_&i.= Sale_&i. - int_&i.
            Max_Value = %sysfunc(max(value_&i.));
        %end;
    run;
%mend maxvalue;
%maxvalue;

I should specify that I only want the max of the four variables for each observation. Thanks for your help!

Answer 1

Aside from the typo - %let i=(A B C D); should be %let list=(A B C D) - you're a) overcomplicating it, and b) confusing macro syntax with datastep syntax. Whilst you could do this using a macro, there is no need.

Given the variables in question are all prefixed in a similar manner (although it would be even better if they were numerically-suffixed, e.g. Value1, Value2), it's far easier to use arrays and the appropriate functions :

data want ;
  set have ;
  array sale{*} Sale_A Sale_B Sale_C Sale_D ;
  array int{*} Int_A Int_B Int_C Int_D ;
  array value{*} Value_A Value_B Value_C Value_D ;

  /* Iterate over array */
  do i = 1 to dim(sale) ;
    value{i} = sum(sale{i},-int{i}) ;
  end ;
  max_value = max(of value{*}) ;
run ;    

Answer 2

Why not just rename your variables to SALE_1 to SALE_4? Then you can reference them with a simple variable list SALE_1-SALE_4.

If you are going to use non-numeric suffixes on lists of similarly named variables then perhaps what you really need is a simple function style macro to generate the lists of variable names based on a base name and list of suffix values.

%macro generate_names(base,list);
&base%sysfunc(tranwrd(%sysfunc(compbl(&list)),%str( ),%str( &base)))
%mend generate_names;

Then it is easier to generate variable lists to use for ARRAY statements

%let suffixes=A B C D;
array sale %generate_names(Sale_,&suffixes);
array int  %generate_names(Int_,&suffixes);
array value %generate_names(Value_,&suffixes);

and other statements.

max_value = max(of %generate_names(Value_,&suffixes)) ;

Answer 3

As aforementioned, you're over-complicating this, but you can achieve what you're trying to do using macro logic by including another for loop within your max_value assignment. This method involves you taking the max of your four variables and a missing value, which should produce the desired result:

%let list = A B C D;

%macro maxvalue;

    data want;
        set have;
            %do j = 1 %to %sysfunc(countw(&list.));
            %let i = %scan(&list.,&j.);
                value_&i.= Sale_&i. - int_&i.
            %end;

            max_value = max(
                %do x = 1 %to %sysfunc(countw(&list.));
                %let y = %scan(&list.,&x.);
                    value_&y.,
                %end; .
            );
    run;

%mend maxvalue;

%maxvalue;