Solved
Contributor
Posts: 46

# how to assign the mode value in a column?

So I have 10 columns and I need to do standardization on the 10   Those are numbers of a specific restaurant you've been to last month .  However, I do want to keep the 0 values.  if I standardize all then my 0 values will be standardized, too, worse, some non-0 values could become 0 after that.

Anyway, the later problem I can fix it.

Now, here comes the main problem, I need to set back the 0s.  I tried to do something like this,

data aa;

set bb;

array cc var1-var10;

do i=1 to 10;

if mode(cc(i))=cc(i) then cc(i)=0;

end;

run;

Please let me know how do I fix this problem.  So basically set the "mode number" of a column as 0 since most of the chance since 0 is always my column mode.

Please let me know how to do this.  Or, maybe in this case, any other alternatives?

Thank you.

Accepted Solutions
Solution
‎03-21-2013 12:50 PM
New Contributor
Posts: 3

## Re: how to assign the mode value in a column?

/* to determine a mode for an array you will need 2 arrays.

I have not seen any documentation on a mode function

but have used procs or a data step to determine it; */

%let elements=10;

data aa;

array mm mode1-mode&elements;

array cc var1-var&elements;

set bb;

do i=1 to (&elements-1);

mm = 0;                               /* set the count of same elements to 1 (itself) */

do j=(i+1) to &elements;      /* scan the rest of the array for this value */
if cc eq cc                      /* if matched then increment the number of times */

then mm + 1;                   /* we encountered the value */

end;

end;

mode=0; elements=0;             /* set the mode and elements to 0 */

do i=1 to (&elements-1);         /* scan through to find the mode */

if mm ge element                /* if this is equal or greater count (see note) */

then do;

if mm gt element or           /* if higher node or */

(mode gt cc and

mm eq element)            /* if equal pick the greater value */

the do;

element = mm;                /* set the new count of elements */

mode = cc;                      /* set the mode value */

end;

end;

end;

/* mode contains the highest (of possible many) mode values.

element contains the number of times the mode appears in the array.

note: to change to the first occurence of mode change the ge to gt */

run;

All Replies
Posts: 3,852

## Re: how to assign the mode value in a column?

Maybe this is what you want.  Standardize each column ignoring zero(by setting them to missing) then switch them back to zero.

* create test data;
data class;
set sashelp.class;
array _n
• _numeric_;

do i = 1 to dim(_n);
if ranuni(1) lt .5 then _n=0;

end;

drop i;
run;
proc print;

run;
*replace zero with missing for stdize;
data classM;
set class;
array _n
• _numeric_;

do i = 1 to dim(_n);
if _n eq 0 then _n = .;

end;

drop i;
run;
*stdize ignoring zeros;
proc stdize out=std data=classm;
var _numeric_;
run;
proc print;

run;
*switch the missing back to zero;
proc stdize data=std out=std0 missing=0 reponly;

var _numeric_;
run;
proc print;

run;

Solution
‎03-21-2013 12:50 PM
New Contributor
Posts: 3

## Re: how to assign the mode value in a column?

/* to determine a mode for an array you will need 2 arrays.

I have not seen any documentation on a mode function

but have used procs or a data step to determine it; */

%let elements=10;

data aa;

array mm mode1-mode&elements;

array cc var1-var&elements;

set bb;

do i=1 to (&elements-1);

mm = 0;                               /* set the count of same elements to 1 (itself) */

do j=(i+1) to &elements;      /* scan the rest of the array for this value */
if cc eq cc                      /* if matched then increment the number of times */

then mm + 1;                   /* we encountered the value */

end;

end;

mode=0; elements=0;             /* set the mode and elements to 0 */

do i=1 to (&elements-1);         /* scan through to find the mode */

if mm ge element                /* if this is equal or greater count (see note) */

then do;

if mm gt element or           /* if higher node or */

(mode gt cc and

mm eq element)            /* if equal pick the greater value */

the do;

element = mm;                /* set the new count of elements */

mode = cc;                      /* set the mode value */

end;

end;

end;

/* mode contains the highest (of possible many) mode values.

element contains the number of times the mode appears in the array.

note: to change to the first occurence of mode change the ge to gt */

run;

Super User
Posts: 13,523

## Re: how to assign the mode value in a column?

What will you do if you have multiple modes for any group?

I have a sneaking suspicion that is one reason why SAS doesn't have a datastep MODE function.

New Contributor
Posts: 3

## Re: how to assign the mode value in a column?

see note (pick either high or pick first) you could also set a flag letting you know you have a multiple mode array.

New Contributor
Posts: 3

## Re: how to assign the mode value in a column?

proc means/summary, univariate, tabulate, and report will generate a mode for columns;

You can do something with the output of a proc freq as well;

🔒 This topic is solved and locked.