topic Re: Cannot join by the same key in SAS Programming

Cannot join by the same key

SASdevAnneMarie — Fri, 01 Aug 2025 16:16:15 GMT

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!

Re: Cannot join by the same key

yabwon — Fri, 01 Aug 2025 16:45:50 GMT

1) what does the LOG says?

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

Bart

Re: Cannot join by the same key

yabwon — Fri, 01 Aug 2025 16:50:21 GMT

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

Re: Cannot join by the same key

SASdevAnneMarie — Fri, 01 Aug 2025 18:11:07 GMT

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;

Re: Cannot join by the same key

Kurt_Bremser — Fri, 01 Aug 2025 20:17:55 GMT

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.

Re: Cannot join by the same key

SASdevAnneMarie — Fri, 01 Aug 2025 21:45:16 GMT

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

Re: Cannot join by the same key

Kurt_Bremser — Sat, 02 Aug 2025 06:04:50 GMT

@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.

Re: Cannot join by the same key

Ksharp — Sat, 02 Aug 2025 08:11:40 GMT

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;

Re: Cannot join by the same key

Kurt_Bremser — Sat, 02 Aug 2025 09:31:11 GMT

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

Re: Cannot join by the same key

SASdevAnneMarie — Sat, 02 Aug 2025 09:45:03 GMT

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

Re: Cannot join by the same key

SASdevAnneMarie — Sat, 02 Aug 2025 09:54:07 GMT

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

Re: Cannot join by the same key

Kurt_Bremser — Sat, 02 Aug 2025 11:18:26 GMT

@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?

Re: Cannot join by the same key

Kurt_Bremser — Sat, 02 Aug 2025 11:21:45 GMT

@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".

Re: Cannot join by the same key

SASdevAnneMarie — Sat, 02 Aug 2025 11:55:41 GMT

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

Re: Cannot join by the same key

SASdevAnneMarie — Sat, 02 Aug 2025 11:56:30 GMT

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

Re: Cannot join by the same key

Kurt_Bremser — Sat, 02 Aug 2025 12:07:14 GMT

It‘s a combination of the k, a and d modifier.

Re: Cannot join by the same key

Ksharp — Sun, 03 Aug 2025 05:19:55 GMT

Kurt already gave you the answer.
'kad' stands for Keep Alphabet Digit characters, and get rid of others characters.