PROC SQL lef join not joining correctly

Corinthian94 — Tue, 22 Aug 2023 16:14:38 GMT

Hi all,

I'm having some issues with the below code and datasets. I am trying to use a proc sql left join to bring the two datasets together with a new variable, but when I do it adds an extra two from the second dataset instead of only joining to the two already existing in the first dataset. Code and pictures of the three datasets in order below. Thank you in advance for the help!

proc sql; create table eligs as select
a. * ,
b. x_dupIPelig label = "Match with other Elig" as eligIP_flag

from eligIP2 a left join eligIPdups b
on a.screener_ID = b.screener_ID;
quit;

eligIP2:

Eligipdups:

Eligs(Created dataset):

Re: PROC SQL lef join not joining correctly

ballardw — Tue, 22 Aug 2023 16:29:35 GMT

That is the basic behavior of a left join: ALL of the records in the "left" set are joined to ALL of the records that match in the "right" set.

Perhaps you need to join on two or more variables such as

proc sql; 
   create table eligs as 
   select  a. * ,
   b. x_dupIPelig label = "Match with other Elig" as eligIP_flag

   from eligIP2 a 
            left join 
            eligIPdups b
    on a.screener_ID = b.screener_ID
        and a.vidnum=b.vidnum
   ;
quit;

topic Re: PROC SQL lef join not joining correctly in SAS Programming

PROC SQL lef join not joining correctly

Re: PROC SQL lef join not joining correctly