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;
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;
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;
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.
Without seeing some test data which matches your requirements, I don't see how we can supply logic?
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.