topic Re: Calculating Mode across variables.. in SAS Programming

Calculating Mode across variables..

Hitesh — Fri, 20 Jun 2014 16:56:03 GMT

I have 20 different values for the same person from different sources (Stored in 20 variables Age1, Age2, ... Age20) and need to select one final value of age. We decided to go with Maximum occurred value (Mode) of age. If the person has one mode then that's the value or if more than one then randomly pick one. To do this I need to calculate Mode across variables as we calculate Mean or Sum(Age1, Age2,...Age20) using SAS Function. I couldn't find similar function for calculating MODE. I can transpose and calculate modes for each sub-group but the problem is dataset is very large (4-million records and 20 variables) and I need their mode recombined to their IDs. So better way is if I can find MODEs. across the variables Is there any way to calculate this?

Re: Calculating Mode across variables..

PaigeMiller — Fri, 20 Jun 2014 17:23:03 GMT

Google finds an example of a SAS program that computes mode

http://www.tek-tips.com/viewthread.cfm?qid=1461025

There's no need to post the same question in multiple forums

Re: Calculating Mode across variables..

Ksharp — Sat, 21 Jun 2014 09:33:52 GMT

OK. Here is :

data x(drop=i j);
 array x{*} a1-a6 ;
 do i=1 to 100;
  do j=1 to dim(x);
   x{j}=ceil(ranuni(0)*20);
  end;
  output;
 end;
run;
data want(drop=k count i max);
if _n_ then do;
 declare hash ha();
 declare hiter hi('ha');
  ha.definekey('k');
  ha.definedata('k','count');
  ha.definedone();
end;
 set x;
  array x{*} a1-a6 ;
do i=1 to dim(x);
 k=x{i};
 if ha.find()=0 then do;count+1;ha.replace();end;
  else do;count=1;ha.add();end;
end;
max=0;
do while(hi.next()=0);
 if count gt max then do;max=count;mod=k;end;
end;
ha.clear();
run;

Xia Keshan

Re: Calculating Mode across variables..

Hitesh — Mon, 23 Jun 2014 17:49:27 GMT

Hi KSharp,

Thank you very much for the reply and codes. This code perfectly works for non-missing observations. I've lots of missing (Like some IDs have only 2-values, some has 4 and some has 10 or 20). These codes generates lots of missing values for MODE if So I need to calculate MODE for available values across columns(Variables) and also want to find out multi-modes if bimodal. I check other softwares and Excel (2010) has these functions MODE.SNGL and MODE.MULT. I am looking for similar results as Number of records are very large and in SAS.

Re: Calculating Mode across variables..

Ksharp — Tue, 24 Jun 2014 13:29:05 GMT

It is for non-missing value.

data x(drop=i j);
 array x{*} a1-a6 ;
 do i=1 to 100;
  do j=1 to dim(x);
   x{j}=ifn(ranuni(0) < 0.2,., ceil(ranuni(0)*20) );
  end;
  output;
 end;
run;
data want(drop=k count i max);
if _n_ then do;
 declare hash ha();
 declare hiter hi('ha');
  ha.definekey('k');
  ha.definedata('k','count');
  ha.definedone();
end;
 set x;
  array x{*} a1-a6 ;
do i=1 to dim(x);
 if not missing(x{i}) then do;
     k=x{i};
     if ha.find()=0 then do;count+1;ha.replace();end;
       else do;count=1;ha.add();end;
 end;
end;
max=0;
do while(hi.next()=0);
 if count gt max then do;max=count;mod=k;end;
end;
ha.clear();
run;

"also want to find out multi-modes if bimodal."

About it. I also can do it, but I need more details and the output you want.

Xia Keshan

Re: Calculating Mode across variables..

Hitesh — Wed, 25 Jun 2014 15:58:19 GMT

Here is Sample data and answer(Output) expectation:

Sample Fake Data Values							Answer expected
ID	Age1	Age2	Age3	Age4	Age5	Age6
A01	10.0	.	.	.	10.0	11.0	10 (Mode Value)
A03	.	.	.	13.1	12.0	13.5	Random one from all different values
A05	.	12.0	12.0	12.0	12.5	13.0	12 (Mode Value)
A07	11.8	11.0	11.0	.	13.0	10.0	11 (Mode Value)
A09	14.0	14.0	10.0	.	10.0	15.0	random one from two (Mode Values)
A11	.	.	17.0	.	16.2	17.0	17 (Mode Value)
A13	11.0	.	11.3	11.0	11.3	11.0	11 (Mode Value)
A15	.	.	.	10.7	11.0	10.7	10.7 (Mode Value)
A17	.	14.0	.	12.1	14.0	12.1	random one from two (Mode Values)
A19	14.0	.	.	.	.	.	14 (Mode Value)
A21	.	12.0	.	.	.	.	12 (Mode Value)
A23	.	.	.	.	12.0	14.0	Random one from all different values
A25	12.5	13.0	12.7	13.5	11.9	12.6	Random one from all different values

Thank you very much for the answer.

Hitesh

Re: Calculating Mode across variables..

Haikuo — Wed, 25 Jun 2014 16:10:48 GMT

Hitesh,

Ksharp is on Beijing time, so he may not be able to get back to you in a while. But Ksharp's new code seems to work out of box. Have you actually tried it?

Regards,

Haikuo

Re: Calculating Mode across variables..

Ksharp — Thu, 26 Jun 2014 12:03:14 GMT

I have already given you solution. Didn't you try it ?

data x;
infile cards truncover;
input ID     $  Age1     Age2     Age3     Age4     Age5     Age6     ;
cards;
A01     10.0     .     .     .     10.0     11.0     10 
A03     .     .     .     13.1     12.0     13.5     
A05     .     12.0     12.0     12.0     12.5     13.0     12 
A07     11.8     11.0     11.0     .     13.0     10.0     11 
A09     14.0     14.0     10.0     .     10.0     15.0     
A11     .     .     17.0     .     16.2     17.0     17 
A13     11.0     .     11.3     11.0     11.3     11.0     11
A15     .     .     .     10.7     11.0     10.7     10.7 
A17     .     14.0     .     12.1     14.0     12.1     
A19     14.0     .     .     .     .     .     14 
A21     .     12.0     .     .     .     .     12 
A23     .     .     .     .     12.0     14.0     
A25     12.5     13.0     12.7     13.5     11.9     12.6
;
run;

data want(drop=k count i max);
if _n_ then do;
 declare hash ha();
 declare hiter hi('ha');
  ha.definekey('k');
  ha.definedata('k','count');
  ha.definedone();
end;
 set x;
  array x{*} age: ;
do i=1 to dim(x);
 if not missing(x{i}) then do;
     k=x{i};
     if ha.find()=0 then do;count+1;ha.replace();end;
       else do;count=1;ha.add();end;
 end;
end;
max=0;
do while(hi.next()=0);
 if count gt max then do;max=count;mod=k;end;
end;
ha.clear();
run;

Xia Keshan

Re: Calculating Mode across variables..

Hitesh — Thu, 26 Jun 2014 15:04:18 GMT

Hi Xia,

Thank you very much. This is very much helpful!

Hitesh

Re: Calculating Mode across variables..

devlekhu_98 — Wed, 02 Jul 2014 21:53:46 GMT

Hi Xia,

Thank you very much for providing the codes.

The code works but gives same value of mode selected (results) all the runs which may causes some bias. To eliminate this bias I want random selection in two different situations, which are:

1. If there are multiple modes (two or three), pick one mode value randomly

2. If there is no mode, pick one value randomly from available values in the columns

Your help is appreciated.

Hitesh

Re: Calculating Mode across variables..

Ksharp — Thu, 03 Jul 2014 12:49:08 GMT

OK. No problem. Let me know if it worked.

 
data x;
infile cards truncover;
input ID     $  Age1     Age2     Age3     Age4     Age5     Age6     ;
cards;
A01     10.0     .     .     .     10.0     11.0     10 
A03     .     .     .     13.1     12.0     13.5     
A05     .     12.0     12.0     12.0     12.5     13.0     12 
A07     11.8     11.0     11.0     .     13.0     10.0     11 
A09     14.0     14.0     10.0     .     10.0     15.0     
A11     .     .     17.0     .     16.2     17.0     17 
A13     11.0     .     11.3     11.0     11.3     11.0     11
A15     .     .     .     10.7     11.0     10.7     10.7 
A17     .     14.0     .     12.1     14.0     12.1     
A19     14.0     .     .     .     .     .     14 
A21     .     12.0     .     .     .     .     12 
A23     .     .     .     .     12.0     14.0     
A25     12.5     13.0     12.7     13.5     11.9     12.6
;
run;

data want(drop=k count i max);
if _n_ then do;
 declare hash ha();
 declare hiter hi('ha');
  ha.definekey('k');
  ha.definedata('k','count');
  ha.definedone();

 declare hash random(ordered:'a');
 declare hiter hir('random');
  random.definekey('ran');
  random.definedata('k','count');
  random.definedone();

end;
 set x;
  array x{*} age: ;
do i=1 to dim(x);
 if not missing(x{i}) then do;
     k=x{i};
     if ha.find()=0 then do;count+1;ha.replace();end;
       else do;count=1;ha.add();end;
 end;
end;
do while(hi.next()=0);
 ran=ceil(ranuni(-1)*1000000);random.add();
end;
max=0;
do while(hir.next()=0);
 if count gt max then do;max=count;mod=k;end;
end;
ha.clear();     random.clear();
run;

Xia Keshan

Re: Calculating Mode across variables..

Hitesh — Mon, 07 Jul 2014 17:38:08 GMT

Hi KSharp,

Sorry for bothering you again..

The program creating Hash/Hiter works perfectly and serves all my purposes for small data..up to 10,000 records but,

for files larger than that gives ERROR Message and stops processing. The error message is:

ERROR: Hash object added 0 items when memory failure occurred.

FATAL: Insufficient memory to execute DATA Step program. Aborted during Execution phase.

ERROR: SAS system stopped processing this step because of insufficient memory.

I've tried two different recommendations:

1. MEMSIZE in config file changed to 4G, didn't help.

2. used HASHEXP:20 in Declare statement "declare hash ha(hashexp:20)" in your syntax.

Please, let me know if you have solution for this problem. I've 3.3Million records and 16 Age variables for each record.

Once again, thank you very much for all your help.

Hitesh

Re: Calculating Mode across variables..

Ksharp — Tue, 08 Jul 2014 12:51:33 GMT

OH, Very sorry about that. I just found a fatal error which could generate your kind error. I should wirte if _n_ eq 1 NOT if _n_ , otherwise SAS will create a Hash Object for every Obs . Sorry about that again.

data want(drop=k count i max ran);

if _n_ eq 1 then do;

declare hash ha();

declare hiter hi('ha');

ha.definekey('k');

ha.definedata('k','count');

ha.definedone();

declare hash random(ordered:'a');

declare hiter hir('random');

random.definekey('ran');

random.definedata('k','count');

random.definedone();

end;

set x;

array x{*} age: ;

do i=1 to dim(x);

if not missing(x{i}) then do;

k=x{i};

if ha.find()=0 then do;count+1;ha.replace();end;

else do;count=1;ha.add();end;

end;

do while(hi.next()=0);

ran=ranuni(-1);random.add();

end;

max=0;

do while(hir.next()=0);

if count gt max then do;max=count;mod=k;end;

end;

ha.clear(); random.clear();

run;

Xia Keshan

Message was edited by: xia keshan

Re: Calculating Mode across variables..

Hitesh — Tue, 08 Jul 2014 14:25:25 GMT

Thank you very much, Xia !

Re: Calculating Mode across variables..

Ksharp — Tue, 08 Jul 2014 14:50:50 GMT

You are welcome. and Don't forget to let me know if it worked .

Re: Calculating Mode across variables..

Hitesh — Tue, 08 Jul 2014 15:25:47 GMT

That worked fine,

Thanks again! You saved my life.

Have great day.

Hitesh

Re: Calculating Mode across variables..

Hitesh — Mon, 21 Jul 2014 18:14:20 GMT

It worked very well..I got the right answer..

Thanks again

Re: Calculating Mode across variables..

Dengyoh — Wed, 01 Jul 2015 03:48:43 GMT

Hi Xie Keshan,

Thanks for sharing your code - I'm new to SAS and still trying to get my head around how the code works but it does!

I was wondering what needs to be changed in the code for it to return the first variable (in this instance age1) if there are multiple modes instead of returning a random value?

Many thanks!

Re: Calculating Mode across variables..

Ksharp — Wed, 01 Jul 2015 13:01:08 GMT

Code: Program


data x;
infile cards truncover;
input ID   $  Age1   Age2   Age3   Age4   Age5   Age6   ;
cards;
A01 10.0 . . . 10.0 11.0 10 
A03 . . . 13.1 12.0 13.5  
A05 . 12.0 12.0 12.0 12.5 13.0 12 
A07 11.8 11.0 11.0 . 13.0 10.0 11 
A09 14.0 14.0 10.0 . 10.0 15.0  
A11 . . 17.0 . 16.2 17.0 17 
A13 11.0 . 11.3 11.0 11.3 11.0 11
A15 . . . 10.7 11.0 10.7 10.7 
A17 . 14.0 . 12.1 14.0 12.1  
A19 14.0 . . . . . 14 
A21 . 12.0 . . . . 12 
A23 . . . . 12.0 14.0  
A25 12.5 13.0 12.7 13.5 11.9 12.6
;
run;
 
data want(drop=k count i idx max );
if _n_ eq 1 then do;
declare hash ha();
declare hiter hi('ha');
  ha.definekey('k');
  ha.definedata('k','count','idx');
  ha.definedone();
 
declare hash random(ordered:'d');
declare hiter hir('random');
  random.definekey('idx');
  random.definedata('k','count');
  random.definedone();
 
end;
set x;
  array x{*} age: ;
do i=dim(x) to 1 by -1;
if not missing(x{i}) then do;
   k=x{i};idx=i;
   if ha.find()=0 then do;count+1;ha.replace();end;
   else do;count=1;ha.add();end;
end;
end;
do while(hi.next()=0);
random.add();
end;
max=0;
do while(hir.next()=0);
if count ge max then do;max=count;mod=k;end;
end;
ha.clear();   random.clear();
run;