this could be due to  primary index. Here name is your primary index. If you do not explicitly mention primary index, then your first column will become primary index. Is your name column unique, if not, it will leading to skewing , which in turn will effect your performance. If I were you, I will do few changes. 1. I will create your volatile table as multiset table with no primary index. (no PI is best for staging tables as data can be inserted fastly because data can be loaded into this tables randomly). Multiset table will not check for  duplicate records further enhancing performance. If you do not mention set or multiset table in ANSI mode, set tables are created, Set tables will check for duplicates ) 2. I will name as varchar(10) instead of char(10) please check the code below.   proc sql; connect to teradata(SERVER=ABCD USER=XXXX PASSWORD=XXXXXX CONNECTION=GLOBAL); execute (CREATE multiset VOLATILE TABLE temp1(NAME VARCHAR(10), STATE CHAR(3)) NO PRIMARY INDEX ON COMMIT PRESERVE ROWS) by teradata; execute ( COMMIT WORK ) by teradata; quit;   ... View more

data want; length claim_id $5. cat $20.; do until (last.claim_id); set have; by claim_id notsorted; cat=catx(',',cat,visit); end; drop visit; run; proc print;run;   My answer as is copy of @Haikuo previous answer shown below. https://communities.sas.com/t5/Base-SAS-Programming/Concatenate-multiple-rows-into-a-single-value/td-p/132882 ... View more

something like this should work   'm/NA|N\/A/oi'   when you have N/A, you need to escape / by adding \ as / has different meaning   data abc; input var1 $; datalines; NA N-A N/A ; data abcd(drop=pat); set abc; pat =prxparse('m/NA|N\/A/oi'); if prxmatch(pat, var1)>0 then newval ='yes'; else newval ='no'; run;     for your query all you need is pat =prxparse('m/NA|N\/A/oi'); if prxmatch(pat, var1)>0 ; ... View more

As SAS dataset is pretty small. Move the SAS dataset to Oracle and build index on the table and collect stats. Then do the update through explicit pass through.       ... View more