How to pick multiple column names for a max value using vname function

[SAS_prep]

Hi,

How do i fetch multiple column name for a value using Vname function. 

/*Input*/
Vid	sep_2017	Oct_2017	Nov_2017	Dec_2017	jan_2018	max
aa	3	0	5	1	0	5
bb	4	4	4	4	2	4
cc	8	3	8	1	2	8
dd	0	0	0	5	1	5
ee	9	2	5	6	9	9

/*expected output**/

Vid	sep_2017	Oct_2017	Nov_2017	Dec_2017	jan_2018	max	Period
aa	3	0	5	1	0	5	Nov_2017
bb	4	4	4	4	2	4	sep_2017,Oct_2017,Nov_2017,Dec_2017
cc	8	3	8	1	2	8	sep_2017,Nov_2017
dd	0	0	0	5	1	5	Dec_2017
ee	9	2	5	6	9	9	sep_2017,Dec_2017

/**Code**/


Data want;
Length period $200.;
Set need;
Array V (*) sep_2017 -- jan_2018 ;
do i = 1 to dim(v)until (i = DIM(V));
if v{i}=Highest_variance then do;
name=vname(v[whichn(Highest_variance, of v{i} )]);
period=CATX(',',period,name);
end;
end;
RUN;

[ballardw]

So where is the value "highest_variance" supposed to come from?

Please describe exactly how you will use that variable with all the comma separated values. Many times when we see this sort of request we get another one a week or two later about pulling that combined value apart for actual use.

[SAS_prep]

Hi,

apologies for the incorrect code.

/*Input*/
Vid	sep_2017	Oct_2017	Nov_2017	Dec_2017	jan_2018	max
aa	3	0	5	1	0	5
bb	4	4	4	4	2	4
cc	8	3	8	1	2	8
dd	0	0	0	5	1	5
ee	9	2	5	6	9	9

/*expected output**/

Vid	sep_2017	Oct_2017	Nov_2017	Dec_2017	jan_2018	max	Period
aa	3	0	5	1	0	5	Nov_2017
bb	4	4	4	4	2	4	sep_2017,Oct_2017,Nov_2017,Dec_2017
cc	8	3	8	1	2	8	sep_2017,Nov_2017
dd	0	0	0	5	1	5	Dec_2017
ee	9	2	5	6	9	9	sep_2017,Dec_2017

/**Code**/


Data want;
Length period $200.;
Set need;
Array V (*) sep_2017 -- jan_2018 ;
do i = 1 to dim(v)until (i = DIM(V));
if v{i}=max then do;
name=vname(v[whichn(max, of v{i} )]);
period=CATX(',',period,name);
end;
end;
RUN;

[Tom]

Your loop is a little convoluted. Why are your using the UNTIL() clause?

Calculate the max value before the loop.

Then you just need to loop over the array and call the VNAME() function when you have hit a copy of the max value.

data want;
  set need;
  length period $200.;
  array V sep_2017 -- jan_2018 ;
  max = max(of v[*]);
  do i = 1 to dim(v);
    if v[i]=max then period=catx(',',period,vname(v[i]));
  end;
run;

[PeterClemmensen]

You can do something like this

data have;
input Vid $	sep_2017	Oct_2017	Nov_2017	Dec_2017	jan_2018 max;
datalines;
aa	3	0	5	1	0	5
bb	4	4	4	4	2	4
cc	8	3	8	1	2	8
dd	0	0	0	5	1	5
ee	9	2	5	6	9	9
;

data temp;
   set have;
   array v{*} sep_2017 -- jan_2018;
   do i=1 to dim(v);
      varname=vname(v[i]);
      value=v[i];
      output;
   end;
run;

proc sql;
   create table temp2 as
      select * from temp
      group by Vid
      having value=max(value);
quit;

data want; 
   do until (last.Vid);
      set temp2;
      Length period $200.;
      by Vid;
      Period=catx(', ', Period, varname);
   end;
   drop i varname value;
run;