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 SQL
explicit pass-through formatted Parallel Group-By with the following EXECUTE statements:
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;