topic Re: max function. which variable/argument contained the max value? in SAS Programming

max function. which variable/argument contained the max value?

mcook — Mon, 29 Jan 2024 00:14:35 GMT

Given a table such as:

data TempMax;
    input ID A B C D E;
    datalines;
        1 234 432 245 555 546
        2 334 258 593 599 113
        3 948 874 737 333 388
        ;
    run;


Obs    ID     A      B      C      D      E

 1      1    234    432    245    555    546
 2      2    334    258    593    599    113
 3      3    948    874    737    333    388

and performing a max function on it

 data TempMax;
        set TempMax;
            latestdate = max(A,B,C,D,E);
        run;

to give

Obs    ID     A      B      C      D      E     latestdate

 1      1    234    432    245    555    546        555   
 2      2    334    258    593    599    113        599   
 3      3    948    874    737    333    388        948

Is there a way to determine which variable was the max? either by identifying the variable, or its argument position in the max function?

Also, the values in the dataset I am working with are dates. and no 2 dates in an obs are equal. so there is one and only one argument to the max function that will be the max.

Re: max function. which variable/argument contained the max value?

Rick_SAS — Mon, 29 Jan 2024 01:15:03 GMT

Yes. I think is easiest if you put the variables into an array and use the OF keyword to supply the values to the MAX function, like this: MAX(of arrayName[*}) .

Anyway, the function you want is the WHICHN function, which returns the (first) index of a list a variables that equals a specified value.

So first compute the max, then use WHICHN to find out which variables (1st, 2nd, 3rd, etc) it matches. If necessary, you can use the VNAME function to get the name of the variable:

/* use OP's data */
data TempMax;
set TempMax;
array myVars[*] A B C D E;  /* or A--E if contiguous */
length varName $32;
date1 = max(A,B,C,D,E);       /* OP's current code */
maxDate = max(of myVars[*]);  /* an equivalent computation that uses arrays */
/* WHICHN function: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p0zs0pv38mel2jn1in4lte2akx4d.htm */
Index = whichn(maxDate, of myVars[*]);
/* VARNAME function: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/n1cjm0xdeczcd5n1s7d8ah9x8432.htm */
varName = vname(myVars[Index]);
run;


proc print data=TempMax; 
   var ID A B C D E maxDate Index varName;
run;

Re: max function. which variable/argument contained the max value?

ballardw — Mon, 29 Jan 2024 11:50:06 GMT

I am a bit confused as to why you are calling your maximum value "latestdate". If your values are actually dates then use dates for your example. When you show values like 234 it makes one suspect that you may not have actual SAS date values and will have other problems later.

data TempMax;
    input ID  A :date9. B :date9. C :date9. D :date9. E :date9. ;
    format a b c d e date9.;
datalines;
1 01JAN2019 03FEB2019 04AUG2020 11NOV2021 12DEC2022
2 15DEC2022 10OCT2022 08AUG2021 06JUN2018 04APR2019
3 11OCT2023 08MAR2018 09FEB2022 22JAN2021 30MAR2017
;

Re: max function. which variable/argument contained the max value?

FreelanceReinh — Mon, 29 Jan 2024 12:54:21 GMT

Alternatively, start with a "long" dataset or create one from your existing data:

proc transpose data=TempMax out=long;
by id;
run;

Then you can use PROC SUMMARY (or PROC SQL) to obtain the desired output quite naturally:

proc summary data=long;
by id;
var col1;
id _name_;
output out=want(drop=_:) max=maxdate maxid=varname;
run;