BookmarkSubscribeRSS Feed
metallon
Pyrite | Level 9

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;

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
metallon
Pyrite | Level 9
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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
metallon
Pyrite | Level 9

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

metallon
Pyrite | Level 9
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 )
)
PGStats
Opal | Level 21

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
LinusH
Tourmaline | Level 20
Please attach some sample data, it would help try your logic.
Data never sleeps
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4881 views
  • 1 like
  • 4 in conversation