topic Re: How to pick multiple column names for a max value using vname function in SAS Programming

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

SAS_prep — Mon, 20 May 2019 15:04:42 GMT

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;

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

ballardw — Mon, 20 May 2019 15:12:35 GMT

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.

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

SAS_prep — Mon, 20 May 2019 15:19:09 GMT

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;

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

PeterClemmensen — Mon, 20 May 2019 15:20:43 GMT

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;

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

Tom — Mon, 20 May 2019 18:00:14 GMT

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;