SAS In-Database Code Accelerator Examples

The following is an example of a DS2 program whose data and thread programs are published and executed in database through the SAS In-Database Code Accelerator. The results from the thread program are processed by the data program inside the database.

options ds2accel=any;

libname teralib teradata server=terapin database=xxxxxx 
   user=xxxxxx password=xxxxxx;
 
data teralib.indata;
   do i = 1 to 10;
      output;
   end;
run;
 
proc ds2;
 
thread th_pgm / overwrite=yes;
   retain isum 0;
   keep isum;
   dcl double x isum;

   method run();
      set teralib.indata;
			x=i+1;
			isum=isum+i;
			end;

   method term();
      output;
    end;
 
endthread;
run;

data out(overwrite=yes);
   retain fsum 0;
   retain nrows 0;
   keep fsum nrows;
 
   dcl thread th_pgm m;
   method run();
      /* The THREADS= argument in the SET FROM statement has no effect */
      /* if the SAS In-Database Code Accelerator is used to access a */
      /* database table. */
      set from m threads=1;
      fsum =fsum + isum;
      nrows = nrows + 1;
   end;
 
   method term();
      output;
   end;
enddata;
run;
quit;

The following example uses formats that are defined in PROC FORMAT. Those formats that are referred to in the thread program are used to create an XML file. In addition to the data and programs, the format XML file is published to the database. The format XML file is used when running DS2 inside the database.

options ds2accel=any;

libname teralib teradata server=terapin database=xxxxxx 
   user=xxxxxx password=xxxxxx;

%let libname=teralib;

data &libname..indata_fmt;
   do i = 1 to 10;
   output;
end;
run;

proc format;
   value yesno 1='YES' 0='NO';
run;

proc format;
   value $x '1'='YES' '0'='NO';
run;

proc ds2;
drop thread th_pgm; run;

thread th_pgm;
   dcl double x;
   dcl char z w;
   method run();
      set &libname..indata_fmt;
      x=i+1;
      z=put(1, yesno.);
      w=put('0', $x.); 
   end;
endthread;
run;

data out (overwrite=yes);
   dcl thread th_pgm m;
   method run();
      dcl double y;
      /* The THREADS= argument in the SET FROM statement has no effect */
      /* if the SAS In-Database Code Accelerator is used to access a */
      /* database table. It could have been omitted from the SET FROM*/
      /* statement. */
      set from m threads=10;
      y=x+1;
   end;
enddata;
run;
quit;

The following output table is produced.

The following example uses user-defined formats and user-defined DS2 packages. In addition to the data and thread programs, the user-defined formats and the user-defined DS2 packages are published to the database.

options ds2accel=any;

libname db teradata user=XXXX password=XXXX 
   server=terapin database=XXXX;

proc ds2;
data db.ipassdata / overwrite=yes;
   declare double  score; 
   method init();
      declare int i;
      do i = 1 to 20;
         score = i * 5;
         output;
      end;
   end;
enddata;
run;
quit;

proc format;
   value lettergrade
      90-high = 'A'
      80-89   = 'B'
      70-79   = 'C'
      60-69   = 'D'
      low-59  = 'F';
run;

proc format;
   value passfail
      70-high = 'PASS'
      low-69  = 'FAIL';
run;

proc ds2;
package pkgGrade;
   method compute(double s) returns char(1);
      declare char(1) g;
      g = put(s, lettergrade.);
      return g;
   end;
endpackage;

package pkgPassFail;
   method compute(double s) returns char(4);
      declare char(4) g;
      g = put(s, passfail.);
      return g;
   end;
endpackage;

thread th_pgm;
    declare char(1) grade;
    declare char(4) pass;
    declare package pkgGrade g();
    declare package pkgPassFail pf();

    method run();
       set db.ipassdata;
       grade = g.compute(score);
       pass  = pf.compute(score);
    end;
endthread;

data outdata;
   dcl thread th_pgm m;
   method run();
      /* The THREADS= argument in the SET FROM statement has no effect */
      /* if the SAS In-Database Code Accelerator is used to access a */
      /* database table. */
      set from m threads=1;
   end;
enddata;
run;
quit;
 
proc print data=outdata; quit;

The following output table is produced.

The following example transposes customer data that has multiple records for each customer into one wide record for each customer. The SAS In-Database Code Accelerator for Teradata redistributes the input data pivot_1m by the first BY variable Cust_Name6. All the rows with the same Cust_Name will be on the same data partition and transposed by one thread.

options ds2accel=any;

%let nobs=1000000;
libname td teradata server=terapin user=xxxxxx password=xxxxxx database=xxxxxx;

proc delete data=td.pivot_1m; run;
data td.pivot_1m (tpt=no fastload=yes dbcommit=100000);
   drop i;
   length Cust_Name $20;
   do i = 1 to &noobs;
       month_id = floor(rand('Uniform')*12)+1;
       month_visits = floor(rand('Uniform')*1000)+1;
       month_amount = (floor(rand('Uniform')*1000000)+1)/100;
       Cust_Name = "Name"||strip(mod(i,1000));
       output;
   end;
run;

%let inputdata=td.pivot_1m;

proc ds2;
   thread work.p_thread / overwrite=yes;
      dcl double i;
      vararray double amount[12]; 
      vararray double num_visits[12];
      keep Cust_Name amount1-amount12 num_visits1-num_visits12;
      retain amount1-amount12 num_visits1-num_visits12;
      method clear_array();
         do i=1 to 12 ;
         amount[i] = 0;
         num_visits[i] = 0;
       end; 
   end;
   method run(); 
      set &inputdata;
       by Cust_Name;
       if first.Cust_Name then 
          clear_array(); 
          amount[month_id] = month_amount + amount[month_id]; 
          num_visits[month_id] = month_visits + num_visits[month_id]; 
       if last.Cust_Name then 
          output; 
   end;
endthread;
run;

data td.pivot_results (overwrite=yes);
   dcl thread p_thread p;
   method run();
      set from p;
      output;
   end;
enddata;
run;
quit;

The following output table is produced (partial output).