User-Defined Formats :: SAS(R) Scalable Performance Data Server 5.1: User's Guide, Second Edition

To create and access user-defined formats in SPD Server, you must do the following::

The user-defined formats must be created on the architecture where they will be used. For example, if the format is to be used on a Windows server, the format must be created on a Windows machine.
The user-defined formats must be created in a domain called formats.
You must make an SPD Server LIBNAME assignment to the domain called formats.
You must specify
```
options fmtsearch=(formats);
```
so that SAS can also find the formats to verify them.
SPD Server does not require that your data and your user-defined formats reside in the same domain. SPD Server will always look in the domain that is named formats when the operating system encounters any call for user-defined formats.

The following example code shows how user-defined formats can be referenced:

in parallel GROUP BY statements
in a WHERE clause within a PROC PRINT step, and
in a WHERE clause referenced in explicit SQL.

The example includes the creation of the user-defined formats and a test table. The example also provides changes to configuration files (spdsserv.parm and libnames.parm) that normally would be made by your SPD Server administrator. For more information about configuring spdsserv.parm files, see Setting Up SAS Scalable Performance Data (SPD) Server Parameter Files in SAS Scalable Performance Data Server: Administrator's Guide. For more information about configuring libname.parm files, see Setting Up SAS Scalable Performance Data (SPD) Server Libname Parameter Files in SAS Scalable Performance Data Server: Administrator's Guide

The example uses the following SPD Server spdsserv.parm file:

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;

The example uses the following SPD Server libnames.parm file:

LIBNAME=tmp pathname=c:\temp;
LIBNAME=formats pathname=c:\data\formats;

Here is the complete example code with comments::

%let domain=tmp; 
%let host=d8488; 
%let serv=5200; 

/* locking=YES must be specified when using */ 
/* options fmtsearch=(formats); */

libname &domain sasspds "&domain"
  host="&host"
  serv="&serv" 
  user='anonymous'
  IP=YES;

libname formats sasspds 'formats' 
  host="&host"
  serv="&serv" 
  user='anonymous'
  locking=YES;
 
options fmtsearch=(formats);

proc datasets nolist 
  lib=formats 
  memtype=catalog; 

delete formats; 
quit;

/* Create AGEGRP and $GENDER formats. */

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 and $GENDER formats.       */

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 Standard 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;

title 'Format in WHERE clause example';
proc print data=temp; 
run;

/* This explicit SQL executes a WHERE clause that */
/* references a user-defined format.              */

title 'Explicit SQL with a User-Defined Format in a WHERE Clause';

proc sql;
connect to sasspds
  (dbq="&domain" 
   serv="&serv" 
   host="&host" 
   user="anonymous");

select * from connection to sasspds
  (select * from fmttest where put(age, AGEGRP.) eq 'Child');
quit;