By default, the columns
of a group-by statement are grouped by their unformatted value. SQL
pass-through parallel GROUP BY provides the capability to also group
data by the columns output data format. For example, you could 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. Grouping by the unformatted value would put these dates
into two separate groups. However, grouping by the formatted month
name, would put these values 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 example code below
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;