Mode

A mode is type of statistic that refers to the most frequently occurring value in a sample set.

Calculating a Mode

Problem

You want to calculate a modal average of the bulb-life results in your database.

Solution:

SELECT COUNT(*) frequency,Hours mode FROM BulbLife
GROUP BY Hours
HAVING COUNT(*)>= ALL(
   SELECT COUNT(*)
   FROM BulbLife
   GROUP BY Hours)

 1) Combine the functionality of DATA and PROC steps into one single step.

 2) sort, summarize, join (merge) and concatenate datasets.

 3) Construct in-line views using the FROM and SELECT clauses.

 4) Line up multiple macro variables using the INTO clause.

The SAS code displayed here can create five sets of macro variables, which are used to create  profiles for 16 states.

proc sql;
select compress(put(count(distinct STATE),best12.))
into :counter
from sashelp.Prdsal2 ;
quit;
%put &counter;

proc sql ;
select state,
min_value,
max_value,
min_act,
max_act
into :state1-:state&counter, :min_value1-:min_value&counter,
:max_value1-:max_value&counter, :min_act1-:min_act&counter,
:max_act1-:max_act&counter
from (select STATE,
min(predict) as min_value,
max(predict) as max_value,
min(actual)-3 as min_act,
max(actual)+3 as max_act
from sashelp.Prdsal2
group by STATE);
quit;

This is an example on efficiency of PROC SQL in creating macro variables other than the advantage of shortening the code. These two macro variables are used for two different reasons. Macro date_newd is used to specify the tick marks of the horizontal axis by day. However, macro date_newis used to label the tick marks on the horizontal axis.

proc sql ;
select quote(put(date_new,date7.)),
quote(put(date_new,date7.))||'d'
into  :date_new separated by ' ',
       :date_newd separated by ' '
from
(select distinct date as date_new
 from sashelp.buy);
quit;