topic Re: how to left join on two variables in SAS Programming

how to left join on two variables

HitmonTran — Tue, 26 Jul 2022 17:41:34 GMT

I want to do a left join on 2 variables.

this is my code but there's an error because of the "on" statement "on a.subject, a.date = b.subject, b.date;"

proc sql;
  create table data3 as  select * 
  from data1 as a left join data2 as b 
  on a.subject, a.date = b.subject, b.date;
quit;

Re: how to left join on two variables

Reeza — Tue, 26 Jul 2022 17:59:03 GMT

proc sql;
  create table data3 as  
  select * 
  from data1 as a 
 left join data2 as b 
  on a.subject = b.subject and a.date=b.date;
quit;

Separate your conditions with an AND

Re: how to left join on two variables

PaigeMiller — Tue, 26 Jul 2022 17:59:42 GMT

proc sql;
  create table data3 as  select * 
  from data1 as a left join data2 as b 
  on a.subject = b.subject and a.date = b.date;
quit;

Re: how to left join on two variables

ballardw — Tue, 26 Jul 2022 22:37:57 GMT

Hint: when you get errors then include the code with the errors, the entire procedure, data step or macro, with all the messages.

In your case the error would have looked like this:

765  proc sql;
766    create table data3 as  select *
767    from sashelp.class as a left join sashelp.class as b
768    on a.sex, a.age = b.sex, b.age;
                       -
                       22
                       76
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS,
              CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS,
              ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

The presence of all the join options tells us that the error is that where the = in your code appears than SAS is expecting something related to another join. The comma was interpreted as a Cartesian join instruction similar to

proc sql;
   select a.*,b.*
   from someset as a, otherset as b
  ;
quit;