07-29-2016 09:57 AM
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
FROM Importkont ik
(COMPRESS(ik.KN_CODE) = pb.CN )
OR ('ex'||COMPRESS(ik.KN_CODE) = pb.CN )
07-29-2016 10:33 AM
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;
08-01-2016 03:57 AM
08-01-2016 04:30 AM
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;
08-01-2016 05:17 AM
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.
08-01-2016 05:31 AM
08-01-2016 02:33 PM
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.
07-29-2016 01:27 PM - edited 08-01-2016 02:08 PM
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.