Hi,   I've created a library in SMC pointing to SASHELP to give me some simple tables to help with some Proof of Concept Stored Processes I'm creating.   As I understand it, the only way to do this is to make the library pre-assigned.  So that was the library template I used.   However, now I'm getting this warning in SAS jobs launched via the Batch Server:   WARNING: The library SASHELP cannot be assigned. The metadata for this library was probably created with the pre-assigned library template. This library cannot be pre-assigned using metadata.   1.  Why does the Batch Server care if the library is pre-assigned?  IOW I don't get what this message is meant to warn me?   2.  How can I get rid of this warning message?   Thanks, Scott     ... View more

Hi,   In this code:   %let server=yourserver; %let authdomain=TeraDevAuth; options sastrace=',,,d' sastraceloc=saslog; /* Set global connection for all tables. */ libname x teradata server=&server authdomain=&authdomain connection=global; /* Create a volatile table. */ proc sql; connect to teradata(server=&server authdomain=&authdomain connection=global); execute (CREATE VOLATILE TABLE temp1 (col1 INT) ON COMMIT PRESERVE ROWS) by teradata; execute (COMMIT WORK) by teradata; quit; /* Insert 1 row into the volatile table. */ proc sql; connect to teradata(server=&server authdomain=&authdomain connection=global); execute (INSERT INTO temp1 VALUES(1)) by teradata; execute (COMMIT WORK) by teradata; quit; /* Access the temporary table through the global libref. */ proc print data=x.temp1; run; /* Create a volatile table with an index. */ proc sql; connect to teradata(server=&server authdomain=&authdomain connection=global); execute (CREATE VOLATILE TABLE temp2 (col1 INT) UNIQUE PRIMARY INDEX (col1) ON COMMIT PRESERVE ROWS) by teradata; execute (COMMIT WORK) by teradata; quit; /* Insert 1 row into the volatile table. */ proc sql; connect to teradata(server=&server authdomain=&authdomain connection=global); execute (INSERT INTO temp2 VALUES(2)) by teradata; execute (COMMIT WORK) by teradata; quit; /* Access the temporary table through the global libref. */ proc print data=x.temp2; run; /* Let SAS do the work of creating the volatile table */ libname x teradata server=&server authdomain=&authdomain connection=global dbmstemp=yes; data x.temp3; col1=3; run; /* Access the temporary table through the global libref. */ proc print data=x.temp3; run; /* A more realistic example */ data x.table_with_sk; sk+1; set sashelp.cars; run; proc print data=x.table_with_sk; run; The 2nd example is explicitly defining the volatile table (VT), including the index definition.  In the 3rd example, I let SAS do the work of creating the VT table definition.  See the trace output in the log.   But, in the 3rd example, is there a way to create an index on the VT?   From the trace output, It appears to me that the teradata engine is intercepting/parsing the SAS code and translating that into teradata SQL.  If so, then couldn't the parser/engine do something with:   data x.temp3 (index=(col1)); col1=3; run; And translate that to (edited sastrace exerpt):   TERADATA_8: Executed: on connection 2 41 1463718006 no_name 0 DATASTEP CREATE VOLATILE MULTISET TABLE "temp3" ("col1" FLOAT) UNIQUE PRIMARY INDEX (col1) ON COMMIT PRESERVE ROWS ;COMMIT WORK 42 1463718006 no_name 0 DATASTEP Regardless of whether I think it should work this way, if there is a way to get SAS to create the table definition per the 3rd example, and also create an index(es), please let me know. ... View more

Hi,   I'm trying to improve the performance of a long running job.  It's using 4 multivalued hash objects to essentially build a Cartesian product with a large fact table.  The job already has to run with a -memsize option due to the hash objects.   After the Cartesian product is built, there are calculations that are CPU bound, that I believe would benefit from DS2 threading.   My question is, if I'm running DS2 threads, can those threads "share" a common hash object?  Or, do these hash objects have to be loaded for each thread?   In pseudocode:     PROC DS2; thread mythread; method run(); set mydata; rc=h.reset_dup(); do while (h.do_over()); /* calculations */ if whatever then output; end; end; endthread; run; data out; dcl thread mythread t; dcl hash h; method init(); /* load hash h */ end; method run(); set from t; end; enddata; run; quit;     That's just rough, quickly thrown together pseudocode from someone new to DS2.  But hopefully the idea of what I want to do is clear?   Thanks, Scott ... View more

Hi,   Apologies for the basic questions.  I've read the first 1/2 of the PROC DS2 Language Reference but still have the below issues.     proc ds2; data class(overwrite=yes); method run(); set sashelp.class; end; enddata; run; quit;     ERROR: Compilation error. ERROR: BASE driver, schema name SASHELP was not found for this connection ERROR: Table "SASHELP.CLASS" does not exist or cannot be accessed ERROR: Line 26: Unable to prepare SELECT statement for table class (rc=0x80fff802U).   How do I make PROC DS2 recognize the pre-allocated SASHELP library (and WORK/other libraries)?     data class; set sashelp.class; run; proc ds2; data class(overwrite=yes); method run(); set class; end; enddata; run; quit;   ERROR: Compilation error. ERROR: Base table or view already exists CLASS ERROR: Unable to execute CREATE TABLE statement for table work.class.   How do I update a table in place using DS2 (if possible)?         proc ds2; data class2(overwrite=yes); method init(); if 0 then set class (locktable=share); end; method run(); set class (keep=(name)); if _n_ le 7 then age=_n_; end; enddata; run; quit;   The "if 0 then set..." paradigm is often used with hash object processing to dynamically set the attributes of the hash object variables.  However, a caveat is the implied retain for data set variables.  This program illustrates the issue; I'd want age=. for _n_ > 7.     proc ds2; data class2(overwrite=yes); method init(); if 0 then set class (locktable=share); end; method run(); call missing(of _all_); * <<< ; set class (keep=(name)); if _n_ le 7 then age=_n_; end; enddata; run; quit;   This is how I usually deal with this issue in my "old style" data step code.  But this yields:   ERROR: Compilation error. ERROR: Missing END statement for the method run. ERROR: Parse encountered CALL when expecting end of input. ERROR: Line 29: Parse failed: >>> call <<< missing(of _all_);   Is there a way to explicitly set a list of variables to missing in DS2?   proc ds2; data test(overwrite=yes); method init(); declare package hash h( [name], [age sex], 16, {select name, age, sex from work.class where sex='F'} ); end; method run(); end; enddata; run; quit; WARNING: Line 27: No DECLARE for referenced variable name; creating it as a global variable of type double. WARNING: Line 28: No DECLARE for referenced variable age; creating it as a global variable of type double. WARNING: Line 28: No DECLARE for referenced variable sex; creating it as a global variable of type double. ERROR: Syntax error at or near "WORK.0" ERROR: Unable to prepare statement for hash data source 0. ERROR: Error reported by DS2 package d2hash: ERROR: Hash data source load failed. ERROR: Fatal run-time error. ERROR: General error   proc ds2; data test(overwrite=yes); method init(); if 0 then set class (keep=(name age sex)); declare package hash h( [name], [age sex], 16, {select name, age, sex from work.class where sex='F'} ); end; method run(); set class (keep=(name)); rc=h.find(); drop rc; end; enddata; run; quit; ERROR: Compilation error. ERROR: Missing END statement for the method init. ERROR: Parse encountered DECLARE when expecting end of input. ERROR: Line 27: Parse failed: >>> declare <<< package hash h(   Any hints on how to code this?   Thanks, Scott                 ... View more

Hi,   I have this code from a SAS-supplied solution macro:   proc sql; update seg_kc.fsc_entity_watch_list_dim set change_end_date=dhms(input(put(&runasofdate,8.),yymmdd8.),00,00,00), change_current_ind = 'N' where upcase(watch_list_name) = 'WCHK' and upcase(change_current_ind) = 'Y' and entity_watch_list_number in (select entity_watch_list_number from STG_WTCH.wl_wchk_delete_load); quit; I've modified it to close out all records, not just those matching the sub-query:   proc sql; update seg_kc.fsc_entity_watch_list_dim set change_end_date=dhms(input(put(&runasofdate,8.),yymmdd8.),00,00,00), change_current_ind = 'N' where upcase(watch_list_name) = 'WCHK' and upcase(change_current_ind) = 'Y'; quit; However, it is taking forever to run.   I've always been taught not to call a function for every record in the dataset/table if that function is in fact just returning a constant.  I was also hoping the below would make PROC SQL use implicit pass-through to Oracle:   %* get runasofdate ; %fcf_get_runasofdate; %put; %put >>> RUNASOFDATE: &runasofdate; %put; %* convert to datetime - this will execute once, at compile time ; %let datetime=%sysfunc(putn(%sysfunc(inputn(&runasofdate,yymmdd8.)),date9.)):00:00:00; %put >>> DATETIME: &datetime; proc sql noprint; update seg_kc.fsc_entity_watch_list_dim set change_end_date = "&datetime"dt, change_current_ind = 'N' where upcase(watch_list_name) in ('WCHK','WORLDCHECK') and upcase(change_current_ind) = 'Y' ; quit; But this is still taking forever.   I can convert this to explicit pass-through, which performs well, but there may be issues with that when crossing environments (DEV/TEST/PROD) - I'd have to investigate that further.   So, is there a way convert the above code to implicit pass-through, or otherwise improve the query performance?   Thanks... ... View more

Hi,   Sorry if this is a dumb question...   In EG, I can click the server (for example SASApp), then click Disconnect to shut down the workspace server session.   Does the same functionality exist in DI Studio?  I've got some user written code which calls user written macros in the autocall library.  When there's an error in the macro, I need to restart the workspace server to reload the autocall macro.   A colleague suggested to close and reopen the job, which appears to work, but I was hoping there's a better way.   Thanks... ... View more