07-18-2016 12:02 PM - edited 07-18-2016 12:05 PM
Hello, I am trying to summarize a dataset that contains both character and numeric data. Here's a very simple version of dataset:
The data is oil and gas fields, but it has multiple entries for each field (for when a oil field has deposits at multiple depths/reservoirs).
I understand how to summarize the numeric data by mean, mode, etc., but I want to be able to summarize the character data with 'modes' or most frequent as well.
Here's what I would want as the output table in the above example:
Is there a function I could use to summarize the character data this way? The real table I would like to summarize has ~100 columns, so hopefully the solution isn't too cumbersome to use.
07-18-2016 12:19 PM
Procs Freq, Report and Tabulate will all count character values and combinations of character values. Using Class statements in Proc Means or summary (an likely the NWAY option) you could also get the the character variables associated with the statistics of one or more of the numerics.
Your example only shows a single value that matches an "input" so it is hard to tell what you were looking for as a result. May be a few more input rows, output and description of the rules you are looking to enforce would help provide a more complete example.
07-18-2016 07:09 PM - edited 07-19-2016 10:51 AM
Do you like to get MODE using a data step? Little programming will take you there. The solution works like this.
 The data set is first sorted by field_name.
 We process records by the BY variable using a Do-Loop(BY-Group Processing).
 We use one array to hold the rock_type and another array to count the number of times rock_type occurs.
 At the end of By-Group, the count[ ] array is searched for the largest value. Using that value, we find the name of the rock_type(rock_type_mode).
 The default output goes to the WANT data set.
data have; input field_name :$9. rock_type :$9. size; datalines; cantarell carbonate 100 cantarell carbonate 100 cantarell clastic 100 lakach clastic 13 lakach clastic 13 lakach carbonate 13 ; run; proc sort data = have; by field_name; run; data want; do i = 1 by 1 until(last.field_name); set have; by field_name; array rock $9. _temporary_; array count _temporary_; do j = 1 by 1 while(1); if rock[j] = rock_type then do; count[j] + 1; leave; end; if missing(rock[j]) then do; rock[j] = rock_type; count[j] = 1; leave; end; end; if last.field_name then do; largest = largest(1, of count[*]); indx = whichn(largest, of count[*]); rock_type_mode = rock[indx]; end; end; call missing(of rock[*], of count[*]); drop i j indx rock_type largest; run; proc print data = want; run;
The ROCK[ ] array is sized to 3 in this case. You can change to any size, even millions - only the memory is the limitation .
Adding some more thoughts ................
I have deleted one unwanted statement ( j =1.
It is possible to have more than one MODE in a distribution like bimode, trimode .. or multimode. The same program can be used. For instance, if you need to get bimode, get first mode using 1 in the first argument in the following statements and use 2 there to get the second mode. Similarly, 3 for trimode.
The do - while Loop is used to search the array in a linear fashion. For large number of rock_type running to thousands search-time will be longer. There are alternatives like the use of FORMAT, Hashing, Binary Search etc. to reduce the run-time.
Do run this with your real data and share with us your experience particulary run-times. There is a SQL solution offered. Compare both the solutions.
largest = largest(1, of count[*]); indx = whichn(largest, of count[*]);
Use of $9:
Here 9 is the length of the longest rock_type in this example. Replace this number by the longest rock_type of your real data. Otherwise truncated strings will not work.
07-19-2016 02:05 AM - edited 07-19-2016 03:02 AM
SQL is good.
data have; input field_name :$9. rock_type :$9. size; datalines; cantarell carbonate 100 cantarell carbonate 100 cantarell clastic 100 lakach clastic 13 lakach clastic 13 lakach carbonate 13 ; run; proc sql; select distinct field_name,rock_type,size from ( select *,count(*) as n from have group by field_name,rock_type ) group by field_name having n=max(n) ; quit;