one more way   proc sql; select id , name, name1 from a left join b on index(catx(',', name1, name2, name3), trim(name)) gt 0; ... View more

you are right  and I have edited my answer ... View more

sorry, i did not see and you need to join back to main query and @Kurt_Bremser  answer is perfect. put you still do not need a subquery for this ... View more

you do not need a subquery at all ... View more

you do not have group by for sum in subquery. so it is doing total for all . try with group by date for subquery. for your code as you do not need a subquery. you can so sum in your query for C. ... View more

it does work for abc's. try out below code data test; length have $ 25; input have 1 - 25; want = compbl(prxchange('s/["'')(,]+/ /',-1, have)); datalines; "abc,'abc' (abc) "abc" (abc) "abc" 'abc' abc, (abc)(abc)(abc)((abc)) (((abc,)))"abc"'abc' abc's ; run; ... View more

if you know variable which you should not include and replace your dataset and libname from the code below proc sql noprint; select cat('max(',trim(name), ') as ', name) into :Val separated by ',' from dictionary.columns where upcase(memname) ="HAVE" and upcase(Libname) = "WORK" and name ne "ID"; %put &val; proc sql; create table want as select id, &val from have group by ID; ... View more

one more way is to use regex   ^(.+_)?  this indicates to start from beginning and go till another instruction in this case till next pattern and this is first pattern   ([^\_]+_[^\_]+)$ indicates anything_anything at the end and is second pattern $2 indicates replace  everything with second pattern data want; set have; var_new = prxchange('s/^(.+_)?([^\_]+_[^\_]+)$/$2/', -1, trim(var)); run;   ... View more

        /* I just indented your code some where your third condition is not right first condition which starts with ON looks syntactically OK 2nd one also looks ok which starts with and 3rd one looks odd, it does not have an and condition and also have does not something to compare to */ ON t1.Name=t1.Name and t1.date= t2.date - day( t1.date) = t2.date ... View more

  data ds1; input var var1 $; datalines; 1 x 2 y ; data ds2; input var var2 $; datalines; 2 J 3 N ; data dsout; merge ds1 (in=a) ds2 (in=b); by var; length dataset $10.; if a then dataset="ds1"; if b then dataset ="ds2"; if a and b then dataset="ds1+ds2"; run; proc sql; create table dsout_sql1 as select coalesce(a.var, b.var) as var, var1, var2, case when a.var =b.var then "ds1+ds2" when a.var = . then "ds2" when b.var = . then "ds1" end as dataset from ds1 a full join ds2 b on a.var = b.var; ... View more

it is Teradata ... View more

    /* this is literal conversion */ ON t1.date - day( t1.date)= t2.date /* you can also use*/ ON Intck('day', t1.date, -day(t1.date)) = t2.date   ... View more