topic Re: Finding mode in multiple columns with groupby in cas in SAS Programming

Finding mode in multiple columns with groupby in cas

Meet26 — Wed, 07 Dec 2022 18:05:36 GMT

I want to find the mode of columns value1,value2 for every group of name,surname columns. as mentioned in below example.

I have various approaches but couldn't able to generate any significant output so have not attached my approch. but here is sample data on which it can be tried.

data table;
infile datalines delimiter=',';
input name $ surname $ value1 value2;
datalines;
N1,S1,90,A
N1,S1,90,B
N1,S1,100,B
N1,S2,70,C
N1,S2,80,C
N1,S2,70,C
;
run;

Re: Finding mode in multiple columns with groupby in cas

ballardw — Wed, 07 Dec 2022 21:59:54 GMT

First thing is make sure the data step generates the data you expect. The Value2 is missing as shown because it has not been read as character.

I don't have CAS so can't show code using it, much less with groupby. However the logic below may help. Caution: This approach requires a separate summary data set for each character variable.

If the values were all numeric proc summary would do this easily. But you have included a character value so you need something a bit more.

One way:

proc sort data=table;
   by name surname;
run;

proc summary data=table;
  by name surname;
  var value1;
  output out=value1 (drop= _:) mode=;
run;

proc freq data=table noprint order=freq;
by name surname;
tables value2/ out=value2 ;
run;

data want;
   merge value1 
         value2 (drop=count percent)
   ;
   by name surname;
   if first.surname;
run;

The order=freq in proc freq places the highest count at the first of each by group. Otherwise you would sort the data by descending count to accomplish the same thing. So when combined with the proc summary output the first match of name and surname is the one you want. Summary will only have one value for each name surname combination.

Re: Finding mode in multiple columns with groupby in cas

s_lassen — Mon, 12 Dec 2022 14:09:53 GMT

Here is another way to generate the data you want, just using PROC SUMMARY:

proc summary data=table order=freq;
  class value1 value2;
  by name surname;
  output out=counts;
run;


data want;
  merge 
    counts(where=(_type_=2) drop=value2)
    counts(where=(_type_=1) drop=value1)
    ;
  by name surname;
  if first.surname;
  drop _:;
run;