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;
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;
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;
pivot_1m
by
the first BY variable Cust_Name6
. All
the rows with the same Cust_Name
are
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;