Sql Join condition using case statement:

[SASPhile]

table1
ColName      
_1abc       
_abc        


table2
Colname     Value
1abc         $300
_abc         $200

When using sql left outer join, on a.colname=b.colname, the first record is not getting value (i.e _1abc from table1) because
of the underscore. (Here a case statement would help or some regex in sql)
So how to bring values from table2 as below:

so the output should be

result:
ColName   Value   
_1abc     $300       
_abc      $200

[Reeza]

Would it be fair to remove the underscores and then do that comparison? This would solve one problem, but may cause others. I can show you how this is done, but I suspect it won't actually work for you because you need a different solution.

Change your join to:
COMPRESS() will remove the underscores only in this.

compress(a.colname, '_') = compress(b.colname, '_')

[SASPhile]

Compressing underscore will compress underscore in any position. Ideally if the first position is underscore and second position is number, ignore firstposition underscore and compare from second psoition, like _1abc and 1abc.

[PGStats · Accepted solution]

If doing the join on fields stripped of any leading underscore is OK:

data t1;
c = "_1abc"; output;
c = "_abc"; output;
run;

data t2;
c = "1abc"; a = 1; output;
c = "_abc"; a = 2; output;
run;

proc sql;
select t1.c, t2.a
from t1 inner join 
    t2 on prxchange("s/^_//o", 1, t1.c) = prxchange("s/^_//o", 1, t2.c);
quit; 

                                 c             a
                                  ---------------
                                  _1abc         1
                                  _abc          2