This example is a sample
test job that validates its own configuration to use user-defined
formats. When properly configured, user-defined formats will allow
columns to be formatted using parallel GROUP BY statements and a WHERE
clause that uses a format to subset data to the server.
The example provides
sample
spdsserv.parm and
libnames.parm file examples, as well as code examples that follow the two sample
SPD Server configuration files.
This example is a sample
test job that checks the usage of user-defined formats. When correctly
set up, user-defined formats will allow formatting of columns in parallel
GROUP BY and permits usage of a WHERE clause that uses a format to
subset data.
SPD Server
spdsserv.parm file used in the example:
SORTSIZE=8M;
INDEX_SORTSIZE=8M;
BINBUFSIZE=32K;
INDEX_MAXMEMORY=8M;
NOCOREFILE;
SEQIOBUFMIN=64K;
RANIOBUFMIN=4K;
NOALLOWMMAP;
MAXWHTHREADS=16;
WHERECOSTING;
RANDOMPLACEDPF;
FMTDOMAIN=FORMATS;
FMTNAMENODE=d8488 ;
FMTNAMEPORT=5200;
SPD Server
libnames.parm file used in the example:
LIBNAME=tmp pathname=c:\temp;
LIBNAME=formats pathname=c:\data\formats;
SPD Server example code:
%let domain=tmp;
%let host=d8488;
%let serv=5200;
/* locking=YES must be specified when using */
/* options fmtsearch=(formats); */
LIBNAME formats sasspds 'formats'
host=&host; serv=&serv;
user='anonymous' locking=YES;
LIBNAME &domain;
sasspds &domain;
host=&host;
serv=&serv;
user='anonymous'
IP=YES;
options fmtsearch=(formats);
PROC DATASETS nolist
lib=formats
memtype=catalog;
delete formats;
quit ;
/* To create user defined formats, they must be */
/* loaded from the same platform where they are */
/* going to be stored. You cannot use Windows */
/* path specifications to load formats on UNIX */
/* platforms. */
/* Add formats to format domain */
PROC FORMAT lib=formats;
value AGEGRP
0-13='Child'
14-17='Adolescent'
18-64='Adult'
65-HIGH='Pensioner';
value $GENDER
'F' = 'Female'
'M' = 'Male';
run ;
/* Create a test table with a column that uses */
/* AGEGRP format */
data &domain..fmttest;
format age AGEGRP. GENDER $GENDER. id z5.;
length GENDER $1;
do id=1 to 100;
if mod (id,2) = 0
then GENDER = 'F';
else GENDER = 'M';
age=int(ranuni(0)*100);
income=age*int(ranuni(0)*1000);
output;
end;
run;
/* 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;