topic Re: How can I get mode of row? in New SAS User

How can I get mode of row?

jorheej — Mon, 19 Sep 2022 07:16:11 GMT

I'm working with over 40 billion rows and 31 columns

and I want to check modes of each rows but I have no idea how to do it..

For example,

A B C D E mode

ㅇ ㅇ ㅇ ㅁ ㅁ ㅇ

Re: How can I get mode of row?

Quentin — Mon, 19 Sep 2022 10:39:06 GMT

Perhaps a hash-table approach like this answer:

https://communities.sas.com/t5/SAS-Programming/Calculate-mode-for-each-row/td-p/468952

Re: How can I get mode of row?

sbxkoenk — Mon, 19 Sep 2022 11:55:33 GMT

Hello,

You can transpose, such that you need to find the mode (modus) for each column.
You can then do that with PROC FREQTAB, which is the CAS-enabled version of PROC FREQ.

But with 40 billion columns you might run into problems (??).
With respect to "SAS: what is maximum number of columns in dataset" --> For all practical purposes, the answer to this question is "unlimited". But that's theory. You will probably need to split into several datasets.

Cheers,

Koen

Re: How can I get mode of row?

Quentin — Mon, 19 Sep 2022 12:08:46 GMT

Yeah, 40B transposing 30 columns is .. a lot. : )

@jorheej , what is the range of each column, and are they integers? Depending on the range, you might be able to calculate the mode with an array, rather than a hash table.

Re: How can I get mode of row?

Ksharp — Mon, 19 Sep 2022 12:15:27 GMT

data have;
input a b c d e f;
cards;
1 2 3 4 5 6
2 2 1 1 1 1
4 5 6 7 8 8
1 1 2 2 2 2
;

data want;
if _n_=1 then do;
length key count 8;
  declare hash h();
  declare hiter hi('h');
  h.definekey('key');
  h.definedata('key','count');
  h.definedone();
end;
set have;
array x{*}  a b c d e f;
h.clear();max=.;
do i=1 to dim(x);
  if not missing(x{i}) then do;
    key=x{i};
 if h.find()=0 then count=count+1;
  else count=1;
 h.replace();
  end;
end;
do while(hi.next()=0);
 if count>max then do;max=count;mode=key;end;
end;
drop i max key count;
run;

BTW, If you have SAS/IML ,that could be more succinct . @Rick_SAS would love to IML code.

Re: How can I get mode of row?

ballardw — Mon, 19 Sep 2022 20:16:48 GMT

Are the variables numeric or character or a mix?

Re: How can I get mode of row?

jorheej — Tue, 20 Sep 2022 08:22:37 GMT

It works for me. Thank you!

Re: How can I get mode of row?

jorheej — Tue, 20 Sep 2022 08:25:25 GMT

Unfortunately, this dataset is not appropriate to use transpose but I'll remember the process of your comment 😄
Thank you for your reply.

Re: How can I get mode of row?

jorheej — Tue, 20 Sep 2022 08:29:01 GMT

they are integers and the range is from 0 to 5,000,000(approximately).
I guess the links above uses both hash table and array and it works!
Thank you for your reply

Re: How can I get mode of row?

jorheej — Tue, 20 Sep 2022 08:33:47 GMT

Hi, thank you so much and I guess the one I tried according to @Quentin is also your solution.
that one works perfectly but it doesn't.
Error was occurred and it says It has to be data or something in statement max=.;

set have;
array x{*} a b c d e f;
h.clear();max=.; <- here

What do you think that I've done wrong?

Re: How can I get mode of row?

jorheej — Tue, 20 Sep 2022 08:35:03 GMT

Some of variables are character but I wanted to use numeric ones only for calculate mode!

Re: How can I get mode of row?

Quentin — Tue, 20 Sep 2022 10:56:43 GMT

I don't see how this bit:

h.clear();
max=.;

Cojuld cause an error, unless you have a character variable named MAX in your input data, and it is character, and you have the undocumented option dsoptions=Note2Err turned on. Which seems unlikely.

Can you post the log from running this step, showing all the code in the step and the error message?

And when you post the log, please click the </> button above the message box. It says "insert code" when you hover, but it's good for inserting any fixed fpnt. It makes it easier to read.

Re: How can I get mode of row?

Ksharp — Tue, 20 Sep 2022 11:37:25 GMT

Can you post your real dataset ? so I can test it .
And also can you post FULL log .

Re: How can I get mode of row?

jorheej — Wed, 21 Sep 2022 05:20:54 GMT

oh i didn't know this function :D

Thank you so much. It turns out I made mistype at the line above.

N0ow those codes work.

Have a nice day !

Re: How can I get mode of row?

jorheej — Wed, 21 Sep 2022 05:22:38 GMT

Sorry for that, the codes were right and I mistyped that ^^;;
Thank you so much for your solutions, I hope you have a great day