Group by a formatted variable in PROC SQL

Question

I am trying to group by custom formatted variable in PROC SQL, but so far have not found a solution. The log has no errors (like a summary statistic error here) and all code is valid. Here's a simple example:

DATA have;
    INPUT value1;
    DATALINES;
1.22
0.99
0.22
4.00
9.99
;
RUN;

PROC FORMAT;
    value valuefmt
        low-.99="Below $1.00"
        1-5="$1-5.00"
        5-high="Above $5.00";
RUN;

DATA have;
    set have;
    FORMAT value1 valuefmt.;
RUN;

PROC SQL;
    SELECT count(*), value1 from have group by value1;
QUIT;

The PROC SQL returns the count grouped by the original value (value1), not the formatted value:

               value1
~~~~~~~~~~~~~~~~~~~~~
       1  Below $1.00
       1  Below $1.00
       1  $1-5.00    
       1  $1-5.00    
       1  Above $5.00

This functionality is allowed in SAS through FREQ or TABULATE. Example:

PROC TABULATE data=have;
    CLASS value1;
    TABLE value1;
RUN;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|                value1                |
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
|Below $1.00 |  $1-5.00   |Above $5.00 |
|~~~~~~~~~~~~+~~~~~~~~~~~~+~~~~~~~~~~~~|
|     N      |     N      |     N      |
|~~~~~~~~~~~~+~~~~~~~~~~~~+~~~~~~~~~~~~|
|        2.00|        2.00|        1.00|
-~~~~~~~~~~~~-~~~~~~~~~~~~-~~~~~~~~~~~~-

Any ideas about how to do something similar with PROC SQL?

Tom Tom · Accepted Answer · 2018-03-22T19:30:25

One way is to use the PUT() function in the GROUP BY clause. You could just return the formatted value.

proc sql;
  select count(*) as N
       , put(value1,valuefmt.) as CharacterValue
  from have
  group by 2
  ;
quit;

Else you will need to add an aggregate function like MIN() if you want to return a value in the original type. You will also need to re-apply the format.

proc sql;
  select count(*) as N
       , min(value1) as Formmatted format=valuefmt.
       , min(value1) as Raw
  from have
  group by put(value1,valuefmt.)
  ;
quit;

Results

   N   Formmatted       Raw
-------------------------------
   2  $1-5.00          1.22
   1  Above $5.00      9.99
   2  Below $1.00      0.22

Group by a formatted variable in PROC SQL

1 Answers