By default, the columns
of a group-by statement are grouped by their unformatted value. You
can use SQL pass-through parallel GROUP BY to group data by the columns
output data format. For example, you can group by the date column
of a table with an input format of mmddyy8 and an output format of
monname9. Suppose the column has dates 01/01/04 and 01/02/04. If you
group by the unformatted value, these dates will be put into two separate
groups. However, if you group by the formatted month name, these values
will be put into the same month grouping of January.
You enable or disable
pass-through formatted parallel group-by with the following execute
commands:
PROC SQL;
connect to sasspds
(dbq=........);
/* turn on formatted parallel group-by */
execute(reset fmtgrpsel)
by sasspds;
select *
from connection
to sasspds
(select dte
from mytable
groupby dte);
/* turn off formatted parallel group-by */
execute(reset nofmtgrpsel)
by sasspds;
select *
from connection
to sasspds
(select dte
from mytable
groupby dte);
quit;
The following example
code is extracted from a larger block of code, whose purpose is to
make computations based on user-defined classes of age, such as Child,
Adolescent, Adult, and Pensioner. The code uses SQL Parallel Group-By
features to create the user-defined classes, and then uses them to
perform aggregate summaries and calculations.
/* Use the parallel group-by feature with the */
/* fmtgrpsel option. This groups the data based */
/* on the output format specified in the table. */
/* This will be executed in parallel. */
PROC SQL;
connect to sasspds
(dbq="&domain"
serv="&serv"
host="&host"
user="anonymous");
/* Explicitly set the fmtgrpsel option */
execute(reset fmtgrpsel)
by sasspds;
title 'Simple Fmtgrpsel Example';
select *
from connection to sasspds
(select age, count(*) as count
from fmttest group by age);
disconnect from sasspds;
quit;
PROC SQL;
connect to sasspds
(dbq="&domain"
serv="&serv"
host="&host"
user="anonymous");
/* Explicitly set the fmtgrpsel option */
execute(reset fmtgrpsel)
by sasspds;
title 'Format Both Columns Group Select Example';
select *
from connection to sasspds
(select
GENDER format=$GENDER.,
AGE format=AGEGRP.,
count(*) as count
from fmttest
formatted group by GENDER, AGE);
disconnect from sasspds;
quit;
PROC SQL;
connect to sasspds
(dbq="&domain"
serv="&serv"
host="&host"
user="anonymous");
/* Explicitly set the fmtgrpsel option */
execute(reset fmtgrpsel)
by sasspds;
title1 'To use Format on Only One Column With Group Select';
title2 'Override Column Format With a Starndard Format';
select *
from connection to sasspds
(select
GENDER format=$1.,
AGE format=AGEGRP.,
count(*) as count
from fmttest
formatted group by GENDER, AGE);
disconnect from sasspds;
quit;
/* A WHERE clause that uses a format to subset */
/* data is pushed to the server. If it is not */
/* pushed to the server, the following warning */
/* message will be written to the SAS log: */
/* WARNING: Server is unable to execute the */
/* where clause. */
data temp;
set &domain..fmttest;
where put
(AGE,AGEGRP.) = 'Child';
run;