Here is an excerpt of a case I opened with SAS TS: I’ve got an ETL process where I’m doing change record detection, and modifying data (close old record, add new record) if a change has occurred.  It’s somewhat analogous to SCD2 processing. I’ve ran into a problem where data was copied into the wrong column.  This error was corrected, but due to the way CATX works, a change is not detected. An example will help to illustrate the point: data test;   length var1 var2 var3 $1 var4 var5 8 buffer1 buffer2 $32767 digest1 digest2 $32;   input var1-var5;   buffer1=catx("|", of var1-var5);   digest1=put(md5(buffer1),hex32.);   buffer2=strip(var1) || "|" || strip(var2) || "|" || strip(var3) || "|" || strip(var4) || "|" || strip(var5);   digest2=put(md5(buffer2),hex32.);    datalines; A B . 1 1 A . B 1 1 ; run; I'd prefer to use the CATX function - I can use a variable list, I don't have to think about data type conversion, and don’t have to jump through hoops in my code generation.  My actual code has about 40 variables I need to check for changes. The solution, of course, is a flag for the CATX function that would mimic the concatenation operator.  IOW, print the delimiter regardless of whether the data value is missing. I think (???) the CATX function could be changed where it would not break existing code.  Example invocations: buffer1=catx("|", of var1-var5, “Y”); buffer1=catx("|", var1, var2, var3, var4, var5, “Y”); The last parameter does not have to be “Y”, it just needs to be some character string, that could not be confused with a variable, that would trigger CATX to print the delimiter regardless of whether the variable is missing or not.  There would be a delimiter for every variable – 1. Questions: Can you think of an ingenious way to address this problem, other than the concatenation operator as above?  Some ideas: * Perhaps build the buffer one variable at a time, translating missing to “00”x, to “trick” CATX into printing the delimiter.  But spinning over the variable list would be problematic since it is a mix of char and num. * Or, perhaps I can export the dataset as a delimited file, read that file back in without delimiters, and use _INFILE_ as the buffer for the MD5 function. * Or, could I use PROC FCMP to roll my own CATX function that would give me the desired results?  (BTW, the desired results is a buffer with one delimiter per variable - 1) Thanks... ... View more

Hi, This post might best be posted to an Oracle forum, but I thought I'd test the waters here.  Also, I hope this is the correct SAS Communities forum - please advise if you disagree.  I don't see a forum for "access to external database" questions. I'm modifying an existing SQL pass through query to Oracle.  The previous query returned either one, or only a small number, of rows from a large Oracle table.  The query was coded as a code generated where clause and then passed to Oracle.  There are two variables in the query (where) criteria, and Oracle is indexed on these variables. I have to modify the query to get the list of records to return from another SAS dataset.  I've chosen to upload that dataset to Oracle temp space, then use an inner query to filter the data (via Oracle pass through). In the previous query, even though the code generation looked "klunky", it would return say 5 records in say 1-2 seconds.  My query is now returning about 1000 records in say 20 minutes. Some example code may help to illustrate the issue.  You'll have to use your imagination for the Oracle pass through bit, but this will run on your system and hopefully this helps explain things better: * simulate Oracle temporary space, which I can write to via SAS/Access to Oracle ; libname tmpora "%sysfunc(pathname(work))"; * this simulates the large table in Oracle ; data work.class;   set sashelp.class; run; * this simulates the upload of the filter criteria to Oracle temp space ; data tmpora.filter;   set sashelp.class;   where name le "L"; run; * option 1- this simulates the existing query ; * this actual code is a bit klunkier than this, but the same approach applies ; * however, this approach is actually quite performant ; * my query could generate 1000's of name/age pairs - I'd have to worry about hitting the macro variable length limit ; * I'd need a different code generation approach. ; proc sql noprint _method;   select catx(" AND ", "(" || cats("NAME=",quote(strip(name))), cats("AGE=",age) || ")") into :where separated by " OR "   from tmpora.filter   ;   %put &where;   create table test1 as   select *   from work.class   where &where   ; quit; * option 2 - this simulates my query rewrite ; * this approach performs much worse than the first approach ; * I suspect, but can't be sure, that Oracle is sorting the large table ; * in order to do the inner join. ; * Is there a "hint" I can give to force Oracle to use the existing index ; * on the large table (name+age)??? ; * Also, is there the equivalent of _method in Oracle SQL to get diagnostic ; * information about how the query is executed??? ; proc sql noprint _method;   create table test2 as   select a.*   from work.class a   inner join tmpora.filter b   on a.name=b.name and a.age=b.age   ; quit; * the two approaches yield the same results ; proc compare base=test1 compare=test2; run; As above, two questions: 1) Is there a "hint" I can give to force Oracle to use the existing index (name+age) on the large table, rather than sorting it? 2) Is there the equivalent of _method in Oracle SQL to get diagnostic information about how the query is executed? Thanks, Scott ... View more