Dynamic Cluster Table Examples

The following four examples show all of the fundamental operations that are required to use dynamic cluster tables.
Example: Create a Dynamic Cluster Table
Example: Add Tables to a Dynamic Cluster
Example: Refresh Dynamic Cluster Table with CLUSTER REPLACE
Example: Refresh Dynamic Cluster Table with CLUSTER REMOVE and CLUSTER ADD
Example: Undo and Refresh Dynamic Cluster Table

Example: Create a Dynamic Cluster Table

The following example creates a dynamic cluster table named Sales_History. The example uses SPD Server tables from the domain Motorcycle.
The first part of the example generates dummy transaction data that is used in the rest of the example. The code creates tables for monthly sales data for 2004 and for the first 6 months of 2005, and then sorts and indexes the data. Next, the code binds twelve individual SPD Server tables for monthly motorcycle sales during 2004 to the dynamic cluster table named Sales_History. 
/* 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
   quit;

Example: Add Tables to a Dynamic Cluster

The following example adds member tables to the dynamic cluster table named Sales_History, which was created in Creating Dynamic Cluster Tables. 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;

Example: Refresh Dynamic Cluster Table with CLUSTER REPLACE

This example performs a dynamic cluster table refresh using the PROC SPDO CLUSTER REPLACE command. The CLUSTER REPLACE command enables you to refresh one member table in a dynamic cluster without interrupting continuous cluster operations by undoing and re-creating the cluster.
The example refreshes the dynamic cluster table named Sales_History. (The example in Creating Dynamic Cluster Tables added additional member tables to the Sales_History table.) 
/* 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 refresh the member tables */
/* in the dynamic cluster table Sales_History */
/* by replacing the member from December 2004 */
/* with a member from January 2005.           */

PROC SPDO library=&domain;
   cluster replace sales_history
      oldmem=sales200412 newmem=sales200501;
   quit;

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

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

Example: Refresh Dynamic Cluster Table with CLUSTER REMOVE and CLUSTER ADD

This example performs a dynamic cluster table refresh using the PROC SPDO CLUSTER REMOVE and CLUSTER ADD command set. The CLUSTER REMOVE and CLUSTER ADD command set enables you to refresh one or more member tables in a dynamic cluster without interrupting continuous cluster operations by undoing and re-creating the cluster.
The example refreshes the dynamic cluster table named Sales_History. (The example in Creating Dynamic Cluster Tables added additional member tables to the Sales_History table.) 
/* 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 refresh the member tables */
/* in the dynamic cluster table Sales_History */
/* by replacing the members from July 2004 to */
/* December 2004 with members from January    */
/* 2005 to June 2005.                         */

PROC SPDO library=&domain;
   cluster remove sales_history
   mem=sales200407
   mem=sales200408
   mem=sales200409
   mem=sales200410
   mem=sales200411
   mem=sales200412';

  cluster add sales_history
   newmem=sales200501
   newmem=sales200502
   newmem=sales200503
   newmem=sales200504
   newmem=sales200505
   newmem=sales200506;
  quit;

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

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

Example: Undo and Refresh Dynamic Cluster Table

This example uses an older SPD Server method to refresh a dynamic cluster table by unbinding the cluster, changing the member tables, and then re-binding the cluster. This method remains functional, and can be used in SPD Server on SAS 9.4 and later. Most users will find the newer SPD Server commands for CLUSTER REMOVE / ADD and CLUSTER REPLACE produce identical results without requiring the dynamic cluster to be disassembled.
The example refreshes the dynamic cluster table named Sales_History. (The example in Creating Dynamic Cluster Tables added additional member tables to the Sales_History table.) First, 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 re-created. When the table is re-created, 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;
  quit;

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

PROC CONTENTS data=&domain..sales_history;
run;
Copyright © SAS Institute Inc. All rights reserved.