User-Defined Formats Example

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;