IMSTAT Procedure (Data and Server Management)

CREATETABLE Statement

The CREATETABLE statement is used to create an empty in-memory table by specifying column definitions. This is useful when you want to append tables, rows, or stream data into and empty table.

Syntax

Required Arguments

CREATETABLE Statement Options

Details

Creating Tables and User-Defined Formats

Syntax

CREATETABLE table-name column-specification1 <column-specification2...> </ options>;

Required Arguments

table-name

specifies the name of the table to create.

column-specification

specifies the name of a column. Numeric columns are created with the name only. Character columns are created by specifying the name and then the column length, enclosing the length in parenthesis.

CREATETABLE Statement Options

HOST="host-name"

specifies the host name for the SAS LASR Analytic Server. Use this option with the PORT= option when you do not have an active table.

LABEL="label"

assigns the specified label to the table.

NOPROXY

specifies that the table creation does not go through the step where the requesting process writes the table signature files. If this option is specified, then the user ID that started the server owns the table signature files.

ORDERBY=(variable-list)

specifies the variable or variables to use for ordering the observations within a partition. This option is ignored unless you also specify the PARTITION= option.

PARTITION=(variable-list)

specifies the variable or variables to use for partitioning the table. Separate variable names with a space.

PERM=mode

specifies the permission setting for accessing a table. The mode value is specified as an integer value such as 755. The mode corresponds to the mode values that are used for UNIX file access permissions.

For Windows servers, the UNIX mode setting is not applicable. Access is controlled according to permissions that you set manually on the signature files directory.

Alias	PERMISSION=
Range	600 to 777

PORT=number

specifies the port number for the SAS LASR Analytic Server. If you do not specify a PORT= value, then behavior of the CREATETABLE statement depends on whether an in-memory table is active. If there is no active table, then the procedure attempts to connect to the server using the LASRPORT macro variable. If a table is active, the information is gathered for the server that is implied by the libref of the active table.

SQUEEZE

specifies to create the in-memory table in compressed form. Rows that are added to the table are compressed by the server as they are added.

Alias	COMPRESS
Interaction	This option cannot be used with both the PARTITION= and ORDERBY= options.

TAG='server-tag'

specifies the tag of the new table in the server. If you do not specify this option, then the default tag, WORK is used. The tag and the value for table-name are used to create the name of the new table in the server.

Details

Creating Tables and User-Defined Formats

If you want to associate the new table with user-defined formats, you can provide the XML for the formats in the FMTLIBXML= option in the PROC IMSTAT statement. The following code provides a simple example.

libname myfmts "/path/to/formats";
proc format library=myfmts;
  value region 00 -< 20 = "Northeast"
               20 -< 45 = "Midwest"
               45 -< 60 = "Southeast"
               60 -< 90 = "West";

  value $storesz 'S' = "Small"
                 'M' = "Medium"
                 'L' = "Large";
run;

options fmtsearch=(myfmts);

filename myfmtxml 'myfmt.xml';
libname  myfmtxml XML92 xmltype=sasfmt tagset=tagsets.XMLsuv;

proc format library=myfmts cntlout=myfmtxml.allfmts; 1
run;

libname example sasiola host="grid001.example.com" port=10010 tag='hps';

proc imstat fmtlibxml=myfmtxml; 2
  createtable sales storeid region size(1) predicted actual / 
     label="Sales Table" tag="hps" host="grid001.example.com" port=10010; 
quit;

/* stream or append data into the table */
data example.sales(append=yes);
  input storeid region size $1. predicted actual;
datalines;
233 10 L 10040 12343
459 33 L 10070 11567
327 45 M 5700  5478
546 76 M 5400  5790
;;;;
run;

proc imstat data=example.sales;
  fetch / format=("", "region.", "$storesz6.", "comma9.", "comma9."); 3
quit;

1	The steps up to this point are common for working with formats such as XML. For a similar example, see Working with User-Defined Formats and the FMTLIBXML= Option.
2	The FMTLIBMXL= option associates the user-defined formats with the table that is created with the CREATETABLE statement. The Region and Storesz formats are not assigned to any variables, but can be applied to variables with any statement that supports the FORMAT= option.
3	The Region and Size variables are formatted with the user-defined formats. The $storesz6. format specifies a length of 6 because that is the longest string in the format definition. If you do not specify the length, then the length of the variable is used and is equivalent to specifying $storesz1. as the format.

The following figure shows the results of the FETCH statement with the user-defined formats applied to the Region and Size variables.

Selected records from the Hps.Sales table