PROC SQL conditional JOIN with OR operator or CASE WHEN

Reply
Frequent Contributor
Posts: 133

PROC SQL conditional JOIN with OR operator or CASE WHEN

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;

Super User
Super User
Posts: 7,401

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

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;
Frequent Contributor
Posts: 133

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

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

Super User
Super User
Posts: 7,401

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

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;
Frequent Contributor
Posts: 133

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

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.

Super User
Super User
Posts: 7,401

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

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

Frequent Contributor
Posts: 133

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

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 )
)
Respected Advisor
Posts: 4,649

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

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.

PG
Super User
Posts: 5,257

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

Please attach some sample data, it would help try your logic.
Data never sleeps
Respected Advisor
Posts: 4,649

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

[ Edited ]

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.

PG
Ask a Question
Discussion stats
  • 9 replies
  • 677 views
  • 1 like
  • 4 in conversation