Dynamic Cluster Table Examples

The following four examples show all of the fundamental operations that are required to use dynamic cluster tables:

Create a Dynamic Cluster Table Example

The following example creates a dynamic cluster table named Sales_History. The first part of the example generates dummy transaction data that is used in the rest of the example.

The example uses SPD Server tables from the domain Motorcycle. Twelve individual SPD Server tables for monthly motorcycle sales during 2004 are bound into the dynamic cluster table named Sales_History. Tables are created for the first six months of motorcycle sales during 2005.

/* declare macro variables that will be used to  */
/* generate dummy transaction data               */
%macro var (varout,dist,card,seed,peak);
    %put &dist; &card; &seed; ;
    %local var1;

    if upcase("&dist;")='RANUNI'
    then do;
       &varout; = int(ranuni(&seed;)*&card;)+1;
    end;
    else
    if upcase("&dist;")='RANTRI'
    then do;
       *%let vartri=%substr("&dist;",5,2)&card; ;
       *&varout;=int(rantri(&seed;,&peak;)*&card;)+1;
       &varout;=int(rantri(&seed;,&peak;)*&card;)+1;
    end;
%mend;

%macro linkvar (varin,varout,devisor);
    &varout;=int(&varin;/&devisor;);
%mend;

/* declare main vars */
%let domain=motorcycle;
%let host=kaboom;
%let port=5200;
%let spdssize=256M;
%let spdsiasy=YES;

LIBNAME &domain; sasspds "&domain;"
   server=&host..;&port;
   user='anonymous'
   ip=YES;

/* generate monthly sales data tables for */
/* 2004 and the first six months of 2005  */
data
   &domain..sales200401;
   &domain..sales200402;
   &domain..sales200403;
   &domain..sales200404;
   &domain..sales200405;
   &domain..sales200406;
   &domain..sales200407;
   &domain..sales200408;
   &domain..sales200409;
   &domain..sales200410;
   &domain..sales200411;
   &domain..sales200412;
   &domain..sales200501;
   &domain..sales200502;
   &domain..sales200503;
   &domain..sales200504;
   &domain..sales200505;
   &domain..sales200506;
   ;

drop seed bump1 bump2 random_dist;

   seed=int(time());

/* format the dummy transaction data */
format trandate shipdate paiddate yymmdd10. ;

put seed;
   do transact=1 to 5000;
      %var (customer,ranuni,100000,seed,1);

      %linkvar (customer,zipcode,10);
      %linkvar (customer,agent,20);
      %linkvar (customer,mktseg,10000);
      %linkvar (agent,state,100);
      %linkvar (agent,branch,25);
      %linkvar (state,region,10);

      %var (item_number,ranuni,15000,seed,1);

      %var (trandate,ranuni,577,seed,1);
      trandate=trandate+16071;

      %var (bump1,ranuni,20,seed,.1);
      shipdate=trandate+bump1;

      %var (bump2,rantri,30,seed,.5);
      paiddate=trandate+bump2;

      %var (units,ranuni,100,seed,1);
      %var (trantype,ranuni,10,seed,1);
      %var (amount,rantri,50,seed,.5);
      amount=amount+25;

      random_dist=ranuni ('03feb2005'd);


      /* sort the dummy transaction data into */
      /* monthly sales data tables            */

      if '01jan2004'd <= trandate <= '31jan2004'd
        then output &domain..sales200401; ;

      else if '01feb2004'd <= trandate <= '28feb2004'd
        then output &domain..sales200402; ;

      else if '01mar2004'd <= trandate <= '31mar2004'd
        then output &domain..sales200403; ;

      else if '01apr2004'd <= trandate <= '30apr2004'd
        then output &domain..sales200404; ;

      else if '01may2004'd <= trandate <= '31may2004'd
        then output &domain..sales200405; ;

      else if '01jun2004'd <= trandate <= '30jun2004'd
        then output &domain..sales200406; ;

      else if '01jul2004'd <= trandate <= '31jul2004'd
        then output &domain..sales200407; ;

      else if '01aug2004'd <= trandate <= '31aug2004'd
        then output &domain..sales200408; ;

      else if '01sep2004'd <= trandate <= '30sep2004'd
        then output &domain..sales200409; ;

      else if '01oct2004'd <= trandate <= '31oct2004'd
        then output &domain..sales200410; ;

      else if '01nov2004'd <= trandate <= '30nov2004'd
        then output &domain..sales200411; ;

      else if '01dec2004'd <= trandate <= '31dec2004'd
        then output &domain..sales200412; ;

      else if '01jan2005'd <= trandate <= '31jan2005'd
        then output &domain..sales200501; ;

      else if '01feb2005'd <= trandate <= '28feb2005'd
        then output &domain..sales200502; ;

      else if '01mar2005'd <= trandate <= '31mar2005'd
        then output &domain..sales200503; ;

      else if '01apr2005'd <= trandate <= '30apr2005'd
        then output &domain..sales200504; ;

      else if '01may2005'd <= trandate <= '31may2005'd
        then output &domain..sales200505; ;

      else if '01jun2005'd <= trandate <= '31jun2005'd
        then output &domain..sales200506; ;
      end ;
run ;

/* index the transaction data in the */
/* monthly sales data tables         */
%macro indexit (yrmth);
   PROC DATASETS library=&domain; nolist;
      modify sales&yrmth; ;
      index create transact customer agent state branch trandate;
   quit;
%mend;

%let spdsiasy=YES;

%indexit (200401);
%indexit (200402);
%indexit (200403);
%indexit (200404);
%indexit (200405);
%indexit (200406);
%indexit (200407);
%indexit (200408);
%indexit (200409);
%indexit (200410);
%indexit (200411);
%indexit (200412);
%indexit (200501);
%indexit (200502);
%indexit (200503);
%indexit (200504);
%indexit (200505);
%indexit (200506);

/* Use PROC SPDO to create the dynamic cluster */
/* table sales_history                         */
PROC SPDO library=&domain; ;
   cluster create sales_history
      mem=sales200401
      mem=sales200402
      mem=sales200403
      mem=sales200404
      mem=sales200405
      mem=sales200406
      mem=sales200407
      mem=sales200408
      mem=sales200409
      mem=sales200410
      mem=sales200411
      mem=sales200412
   maxslot=36;
quit;

Add Tables to a Dynamic Cluster Example

The following example adds member tables to the dynamic cluster table named Sales_History. The Sales_History table currently contains 12 members. Each member is an SPD Server table that contains monthly sales data. This example augments the 12 member tables for 2004 with six new member tables that contain sales data for January through June of 2005.

/* declare main vars */
%let domain=motorcycle;
%let host=kaboom;
%let port=5200;
%let spdssize=256M;
%let spdsiasy=YES;

LIBNAME  &domain; sasspds &domain;
   server=&host..;&port;
   user='anonymous'
   ip=YES;

/* Use PROC SPDO to add member tables to */
/* the dynamic cluster table sales_history */

PROC SPDO library=&domain;
   cluster add sales_history
   mem=sales200501
   mem=sales200502
   mem=sales200503
   mem=sales200504
   mem=sales200505
   mem=sales200506;
quit;

/* Verify the presence of the added tables */
PROC CONTENTS data=&domain..sales_history;
run;

Undo Dynamic Cluster Table Example

The undo example is included as part of the following refresh example.

Refresh Dynamic Cluster Table Example

Refreshing SPD Server dynamic cluster tables is a combination of two tasks, UNDO CLUSTER and CREATE CLUSTER. The UNDO CLUSTER command unbinds an existing dynamic cluster table. The CREATE CLUSTER command rebinds the dynamic cluster table with updated member tables. The following example shows both the UNDO CLUSTER and CREATE CLUSTER commands with SPD Server dynamic cluster tables.

The example refreshes the dynamic cluster table named Sales_History. The Sales_History table received additional member tables in the previous example. The 18-member dynamic cluster table Sales_History is unbound. The 12 member tables that contain 2004 sales data are deleted when the dynamic cluster table Sales_History is recreated. When the table is recreated, only the six member tables that contain 2005 sales data are included. These combined actions refresh the contents of the dynamic cluster table Sales_History.

/* declare main vars */
%let domain=motorcycle;
%let host=kaboom;
%let port=5200;
%let spdssize=256M;
%let spdsiasy=YES;

LIBNAME &domain; sasspds &domain;
   server=&host..;&port;
   user='anonymous'
   IP=YES ;

/* Use PROC SPDO to undo the existing dynamic */
/* cluster table Sales_History, then rebind */
/* it with members from months in 2005 only */

PROC SPDO library=&domain;
   cluster undo sales_history;
   cluster create sales_history
      mem=sales200501
      mem=sales200502
      mem=sales200503
      mem=sales200504
      mem=sales200505
      mem=sales200506
   maxslot=36;
quit;

/* Verify the contents of the refreshed dynamic */
/* cluster table sales_history */

PROC CONTENTS data=&domain..sales_history;
run;