Cannot join by the same key

[SASdevAnneMarie]

Hello Experts,

I have an anormal join result, for example, the key L004IV is common, but the result of left join is empty. I tried left, compress, right option, but it doesn't work.

My code is :

proc sql;
	create table ACCORD13_1 as select  a.*, b.* 
	from ACCORD2_q as a 
	    left join DONNEEMAQUETTE3_q as b on left(a.CD_PRODUIT)=left(b.CD_PRODUIT)
	;
quit;

The data is joined. 

Thank you very much for your help!

[yabwon]

1) what does the LOG says?

2) run proc contents on ACCORD2_q and DONNEEMAQUETTE3_q and share what you see.

Bart

[yabwon]

OK, I think I got it.

1) Run:

options validvarname=any;

before you run that SQL.

2) naming a variable "Frais d'ahésion à l'assos" in SAS is rather silly idea...

Bart

[SASdevAnneMarie]

Hello,

Thank you for your message. I did this, but I still have an empty value for key L004IV :

data ACCORD2_q;
	set ACCORD2;
	rename 'Frais d''ahésion à l''assos'n=Frais_dadhesion;
	keep cd_produit 'Frais d''ahésion à l''assos'n;
run;

data  DONNEEMAQUETTE3_q;
	set DONNEEMAQUETTE3;
	keep  cd_produit Nbcar_arb Nbcar_cngmnt;
run;

options validvarname=any;

/***BASE FINALE***/
proc sql;
	create table ACCORD13_1 as select  a.*, b.* 
		from ACCORD2_q as a 
			left join DONNEEMAQUETTE3_q as b on left(a.CD_PRODUIT)=left(b.CD_PRODUIT)
	;
quit;

[Kurt_Bremser]

Maxim 3: Know Your Data.

Whenever you have such issues, look what's really in there.

First, I filtered your datasets for the problematic key and found that it's mssing in donnemarquette.

Then, I looked at the hex codes:

data check_b;
set DONNEEMAQUETTE3_q;
checkvar = put(cd_produit,$hex16.);
run;

and, lo and behold, in the failing keys there's a hidden 0D0A in the last two characters.

[SASdevAnneMarie]

Thank you Kurt! Did you use the notepad to see the hidden 0D0A?

[Kurt_Bremser]

---

@SASdevAnneMarie wrote:
Thank you Kurt! Did you use the notepad to see the hidden 0D0A?

---

No. Just look at the check dataset in SAS, it contains a variable where the hex codes are displayed.

Alternatively, you can assign a $HEX format to the variable in question.

[Kurt_Bremser]

And the real question is: how can these special characters contaminate your data?
Review your data sources and import processes.

[SASdevAnneMarie]

I don't know, I received this input file.

[Kurt_Bremser]

---

@SASdevAnneMarie wrote:
I don't know, I received this input file.

---

It didn't fall right out of the sky, now did it?

Somebody sent it to you, and that person must be notified that their process is broken. If you intend to be a professional, you can't let this go on.

If you can't even trust their key values, why trust anything else?

[SASdevAnneMarie]

The person copy and past values manually so it can’t be improved.

[Ksharp · Accepted solution]

Kurt was right. You has '0D0A'x characters at the end of variable 'CD_PRODUIT' in dataset 'DONNEEMAQUETTE3_q' .

54   data x1;
55    set ACCORD2_q;
56    if compress(CD_PRODUIT,,'kad')='L004IV';
57    put CD_PRODUIT $hex32.;
58   run;

4C30303449562020



59   data x2;
60    set DONNEEMAQUETTE3_q;
61    if compress(CD_PRODUIT,,'kad')='L004IV';
62     put CD_PRODUIT $hex32.;
63   run;

4C30303449560D0A

Therefore, you need to use function COMPRESS to get rid of these characters:

proc sql;
	create table ACCORD13_1 as select  a.*, b.* 
	from ACCORD2_q as a 
	    left join DONNEEMAQUETTE3_q as b on compress(a.CD_PRODUIT,,'kad')=compress(b.CD_PRODUIT,,'kad')
	;
quit;

[SASdevAnneMarie]

Thank you, Ksharp!
What means the 2d and 3d option (,,'kad') in compress(b.CD_PRODUIT,,'kad')?

[Kurt_Bremser]

---

@SASdevAnneMarie wrote:
Thank you, Ksharp!
What means the 2d and 3d option (,,'kad') in compress(b.CD_PRODUIT,,'kad')?

---

Maxim 1: Read the Documentation.

COMPRESS Function 

See sections "characters" and "modifiers".

[SASdevAnneMarie]

Thank you, I did. I didn’t find « kad » modifier.