proc sql join and data merge both do not work

[Cruise]

Hi Folks and @PGStats ,

I perform following joins many times a day to slightly exaggerate but it doesn't work today. I know that linkage variables have to have the same alignment and the format. I tried A and B in the code snippet with a variation of using LEFT(), RIGHT() and also am sure fips_tract, agecat and sex have identical length, format and informat.

Full join creates 70,338 observations and proc means produce min and max of count outcome correct. However, 

Left join, the join form I need creates 68,684 observations and proc means produce missing values. 

The difference 1654 is the size of one of datasets here.

What am I missing here? Any idea?

Thanks a lot in advance.

PROC MEANS DATA=TR_EJ_KAPOSI MIN MAX MAXDEC=1; VAR N_TRACT; RUN;

A.
PROC SQL; 
CREATE TABLE TR_EJ_KAPOSI AS SELECT
P.TRACT_POP, T.N_TRACT,P.FIPS_TRACT, P.AGECAT,P.SEX
FROM E.POP_AGESEX_RACE_POV_EJ1 P FULL JOIN TRACT_KAPOSI T 
ON RIGHT(P.FIPS_TRACT)=RIGHT(T.FIPS_TRACT) AND RIGHT(P.AGECAT)=RIGHT(T.AGECAT) AND RIGHT(P.SEX)=RIGHT(T.SEX);
QUIT; 

B.
PROC SQL;
CREATE TABLE TR_EJ_KAPOSI AS SELECT
P.TRACT_POP, T.N_TRACT,P.FIPS_TRACT, P.AGECAT,P.SEX
FROM E.POP_AGESEX_RACE_POV_EJ1 P LEFT JOIN TRACT_KAPOSI T 
ON P.FIPS_TRACT=T.FIPS_TRACT AND P.AGECAT=T.AGECAT AND P.SEX=T.SEX;
QUIT; 

[PaigeMiller]

In general, FULL JOIN and LEFT JOIN don't have to produce the same results.

When they don't produce the same results, this implies that the data sets have mismatches on the variables used in the join.

[ErikLund_Jensen · Accepted solution]

Hi @Cruise 

There must be something wrong with your input data.

You get these results if your result data has only missing values in var. N_TRACT, which happens with a left join where the condition is never true.

But with a full join you get extra observations from TRACT_KAPOSI where the condition is not met, and these observations comes with values in N_TRACT. it follows that TRACT_KAPOSI must have 1654 observations.

So even if FIPS_TRACT, AGECAT and SEX have same lengths, types and formats in both data sets, there are no observations with same values of FIPS_TRACT, AGECAT and SEX in both data sets, and therefor no contribution from TRACT_KAPOSI in a left join.

[Cruise]

Thanks a lot. This happened when I re-used my old codes without reviewing first. I changed my unit of analysis from block group to a tract and the last digit for the block group was not removed to become a fips_tract. Thank again.