User-Defined Formats :: SAS® Scalable Performance Data Server 5.3: User's Guide

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.
The LIBNAME assignment cannot be a temporary assignment that uses the TEMP=YES LIBNAME option. All user-defined formats must be in the same physical location that is defined by the formats domain.
The LIBNAME assignment must use the LOCKING=YES setting. The LOCKING=YES setting enables SPD Server to synchronize concurrent read calls to the user-defined formats.
You must set the FMTSEARCH= system option in the SAS session so that SAS can also find the formats to verify them as in this example:
```
options fmtsearch=(formats);
```
SPD Server does not require that your data and your user-defined formats reside in the same domain. The 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
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 SAS Scalable Performance Data Server: Administrator’s Guide.

The example uses the following 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=5400;

The example uses the following 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=5400; 

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

libname &domain sasspds "&domain"
  host="&host"
  serv="&serv" 
  user='anonymous'
  password='mypwd'
  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;