DATA Step, Macro, Functions and more

Left join not working

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

Left join not working

I have a left join that is working like a normal join
Proc sql;
Create table have as
(Select
A.dny,
A.type,
A.orderdste,
B.type,
B.delivery,
B.dny
From table a left join table b on a.dny=b.dny
Where a.orderdste =b.delivery
);
Run;

The output it should bring everything from table a I'm looking for all the dny in table a that are not in table b ...

Accepted Solutions
Solution
‎04-20-2017 03:43 PM
Respected Advisor
Posts: 4,919

Re: Left join not working

Your query should read:

 

Proc sql;
Create table want as
Select
	A.dny,
	A.type,
	A.orderdste,
	B.type
From 
	test as A left join 
	test1 as B on A.dny=B.dny and A.orderdste=B.delivery;
quit;
PG

View solution in original post


All Replies
Frequent Contributor
Posts: 78

Re: Left join not working

 
PROC Star
Posts: 7,467

Re: Left join not working

You don't need to state your request as a subquerry AND, more importantly, your code will produce the wrong results the way you have your input tables defined in your from statement. You are defining a file called table, referring to it as A, and left joining it with itself.

 

Also you don't need the warning you'll get for selecting the columns (with the same names) from the two files. No need to select them from the second file.

 

What are your two input datasets called? TableA and TableB, or A and B

 

Art, CEO, AnalystFinder.com

 

 

 

 

 

Frequent Contributor
Posts: 78

Re: Left join not working

Hi Art I'm using generic name for tables
Proc sql;
Create table have as
(Select
A.dny,
A.type,
A.orderdste,
B.type,
B.delivery,
B.dny
From test A left join test1 b on a.dny=b.dny
Where a.orderdste =b.delivery
);
Run;
PROC Star
Posts: 7,467

Re: Left join not working

Not sure what you mean by "generic table names", but your where statement seems to contradict what you said you wanted. Does the following do what you expect?:

 

data test;
  informat orderdste date9.;
  input dny type orderdste;
  cards;
1 1 20jan2017
2 3 26jan2017
3 2 2feb2017
4 4 3mar2017
;

data test1;
  informat delivery date9.;
  input dny type delivery;
  cards;
1 1 20jan2017
4 4 3mar2017
;

proc sql;
  create table have as
    Select
      A.dny,
      A.type,
      A.orderdste
        From test a left join test1 b
          on a.dny=b.dny
            where b.delivery is null
  ;
quit;

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 78

Re: Left join not working

I change the name of tables to not be confusing. Thanks art
Frequent Contributor
Posts: 78

Re: Left join not working

Art,
The Dny. Is What I'm looking fro m test and where I match from test1 both table will always have date but in some cases Dny won't be in test1 but will be in test....reason for left join ...hope that helps explain what I need
Respected Advisor
Posts: 4,919

Re: Left join not working

You are not getting all records from table a because the where condition a.orderdste = b.delivery is false when there is no matching record in table b.

 

Use 

 

From table a left join table b on a.dny=b.dny and a.orderdste=b.delivery

 

intead.

PG
Frequent Contributor
Posts: 78

Re: Left join not working

Pgstats sorry not sure what I'm doing wrong I should get 3 but only get 2. Results ... the date will always be there... the dny won't be on both tabled it will be in test could be because of the dny=dny where it has to equal to Dny?
PROC Star
Posts: 7,467

Re: Left join not working

Given the two test files I posted earlier, show us which records you expect to get and why.

 

Art, CEO, AnalystFinder.com

 

Solution
‎04-20-2017 03:43 PM
Respected Advisor
Posts: 4,919

Re: Left join not working

Your query should read:

 

Proc sql;
Create table want as
Select
	A.dny,
	A.type,
	A.orderdste,
	B.type
From 
	test as A left join 
	test1 as B on A.dny=B.dny and A.orderdste=B.delivery;
quit;
PG
Frequent Contributor
Posts: 78

Re: Left join not working

Here is the data in the tables
Test
Dny. type. ordered
123 I. 04/18/17
134. I. 04/18/17
145. I. . 04/18/17


Test1
Dny. Type. Delivery
123. I. 04/18/17
134. I. 04/18/17
456. I. 04/18/17
567. I. 04/18/17
I want output to bring everything where we match test and test1
Frequent Contributor
Posts: 78

Re: Left join not working

Thank you both for the assistance
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 316 views
  • 1 like
  • 3 in conversation