topic Re: Sql Join condition using case statement: in New SAS User

Sql Join condition using case statement:

SASPhile — Fri, 11 Jan 2019 02:39:29 GMT

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

Re: Sql Join condition using case statement:

Reeza — Fri, 11 Jan 2019 02:56:36 GMT

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, '_')

Re: Sql Join condition using case statement:

SASPhile — Fri, 11 Jan 2019 03:02:57 GMT

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.

Re: Sql Join condition using case statement:

PGStats — Fri, 11 Jan 2019 04:23:36 GMT

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