topic Re: Return variable with Maximum Value in SAS Procedures

Return variable with Maximum Value

ddeb — Thu, 10 Mar 2011 20:43:34 GMT

I have data of the form:

ID Sales_JAN Sales_FEB Sales_MAR etc....

I know that I can get the maximum sales value for each ID as:
MAX_SALES = MAX (OF SALES:);

I also want to know which month the maximum sales happened in (the variable name which corresponds to the maximum value). I cannot figure out how to do this efficiently. I'm sure SAS has a way of doing this (it has a way of doing most things). If JAN and FEB had the same sales and they were the MAX value, how are ties handled?

Re: Return variable with Maximum Value

data_null__ — Thu, 10 Mar 2011 21:26:34 GMT

WHICHN Function

--------------------------------------------------------------------------------

Searches for a numeric value that is equal to the first argument, and returns the index of the first matching value.

Re: Return variable with Maximum Value

SPR — Fri, 11 Mar 2011 16:19:48 GMT

Hello Ddeb,

You can add to you datastep writing MAX_SALES into a macro variable like this:

call SUMPUTX('Max',MAX_SALES);

and then in a separate datastep:

array{*} S sales:;
do i=1 to hbound{S};
if s[i]=&Max then Max_month=i;
end;

get month number for maximum sales.

Sincerely,
SPR