topic Re: PROC SQL conditional JOIN with OR operator or CASE WHEN in SAS Programming

PROC SQL conditional JOIN with OR operator or CASE WHEN

metallon — Fri, 29 Jul 2016 13:57:26 GMT

Hi SAS Experts,

I got a simple PROC SQL statement that does a conditional joining to another table.
The problem is that the OR is not treated sequentially but more like an AS WELL AS.
The data gets multiplied. But I need it as an EITHER rather than an AS WELL AS.

I tried the same with a CASE WHEN but no luck. The first works fine, 5 rows but the second produces 5 rows again although it’s a clear non-match. Only the first condition actually works.

proc sql ;
CREATE TABLE Import AS

       SELECT DISTINCT
           X
                     FROM Importkont ik
                LEFT JOIN
                p_import pb
                ON

                (COMPRESS(ik.KN_CODE) = pb.CN )

       OR ('ex'||COMPRESS(ik.KN_CODE) = pb.CN )

               ;
QUIT;

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

RW9 — Fri, 29 Jul 2016 14:33:42 GMT

Two things, first there is no test data (form of a datastep) to test this on, so this is untested, secondly code formatting makes code far easier to read. Now for your problem (and no test data so can't say), can you not just strip out the EX from the other variable. It looks like your attempting to merge all of IK to PB, regardless of wether the start with EX or not, so just remove that bit:

proc sql; 
  create table import as
  select  distinct X
  from    IMPORTKONT IK
  left join P_IMPORT PB 
  on      strip(IK.KN_CODE)=strip(tranwrd(PB.CN,"ex",""));
quit;

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

LinusH — Fri, 29 Jul 2016 14:34:46 GMT

Please attach some sample data, it would help try your logic.

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

PGStats — Mon, 01 Aug 2016 18:08:37 GMT

EITHER, meaning when either condition match but not both would be

ON (COMPRESS(ik.KN_CODE) = pb.CN) EQ ('ex'||COMPRESS(ik.KN_CODE) = pb.CN )

but both conditions can never match at the same time. So, AS WELL AS should give the same result as EITHER.

Please illustrate the problem further with some test data and expected results.

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

metallon — Mon, 01 Aug 2016 07:57:02 GMT

Hi everyone,

Thanks for the replies!
I try to first match without adding 'ex' that is the number one priority join codintion
only then I want to join whatever could not be joined by adding 'ex' and seeing if there
is a match.

Your suggestion

" (COMPRESS(ik.KN_CODE) = pb.CN )

OR ('ex'||COMPRESS(ik.KN_CODE) = pb.CN ) "

did not improve my result.

Addressing the urgend call for test data. Here it comes:

left table ik

08022200
08022200

right table (pb)

08022200
ex08022200

expected result:

ik pb
08022200 08022200

attained result:

ik pb
08022200 08022200
08022200 ex08022200

I need a join condition that does not look at a second condition if the first
one matched. ex08022200 should have never been joined with 08022200 because 08022200=08022200 worked
and I dont want that the join considers the second condition i.e. 'ex'||08022200=08022200

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

RW9 — Mon, 01 Aug 2016 08:30:02 GMT

A join considers all the conditionals when evaulating each row. You can split it up by (). However in the example you gave, what you seem to be looking for is a distinct list. To do your join, process your incoming data such that it matches the criteria you want to achieve. Also note, put test data as a datastep so we can see what the data is:

data ik;
  length kn_code $50;
  kn_code="08022200"; output;
  kn_code="08022200"; output;
run;

data pb;
  length kn_code $50;
  kn_code="08022200"; output;
  kn_code="ex08022200"; output;
run;

proc sql;
  create table WANT as
  select  distinct
          IK.KN_CODE as IK_KN_CODE,
          PB.KN_CODE as PB_KN_CODE
  from    (select strip(tranwrd(KN_CODE,"ex","")) as KN_CODE from IK) IK
  full join (select strip(tranwrd(KN_CODE,"ex","")) as KN_CODE from PB) PB
  on      IK.KN_CODE=PB.KN_CODE;
quit;

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

metallon — Mon, 01 Aug 2016 09:17:54 GMT

Hi,

I am sorry but the DISTINCT wont work. That is the problem with providing "test" data. It is hardly ever complete.

There are many more rows that make the rows truly unique. I need a prioritiested conditional join handling.

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

RW9 — Mon, 01 Aug 2016 09:22:42 GMT

Without seeing some test data which matches your requirements, I don't see how we can supply logic?

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

metallon — Mon, 01 Aug 2016 09:31:14 GMT

Abstract thinking. I believe in you. You can do it!

This is a solution I just came up with 2 minutes ago. I basically exclude with a subquery all the rows
that did match in the first join. But its a bit lame I think:
..
ON
COMPRESS(ik.KN_CODE) = pb.CN
OR
(
'ex'||COMPRESS(ik.KN_CODE) = pb.CN AND ik.KN_CODE NOT IN (SELECT ik.KN_CODE FROM yyy ik INNER JOIN xxx pb ON COMPRESS(ik.KN_CODE) = pb.CN )
)

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

PGStats — Mon, 01 Aug 2016 18:33:14 GMT

I would try:

proc sql;
CREATE TABLE import AS
SELECT coalesce(ex.X, pb.X) as X
FROM 
    Importkont as ik LEFT JOIN 
    p_import as ex on cats("ex", ik.KN_CODE) = ex.CN left join
    p_import as pb ON COMPRESS(ik.KN_CODE) = pb.CN;
quit;

and add DISTINCT if needed.