topic Left join not working in SAS Programming

Left join not working

Gil_ — Thu, 20 Apr 2017 16:38:12 GMT

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

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 16:38:20 GMT

Re: Left join not working

art297 — Thu, 20 Apr 2017 17:04:25 GMT

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

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 17:10:09 GMT

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;

Re: Left join not working

art297 — Thu, 20 Apr 2017 17:19:36 GMT

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

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 17:57:01 GMT

I change the name of tables to not be confusing. Thanks art

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 18:11:50 GMT

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

Re: Left join not working

PGStats — Thu, 20 Apr 2017 18:14:09 GMT

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.

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 18:25:06 GMT

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?

Re: Left join not working

art297 — Thu, 20 Apr 2017 19:03:10 GMT

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

Art, CEO, AnalystFinder.com

Re: Left join not working

PGStats — Thu, 20 Apr 2017 19:10:42 GMT

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;

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 19:18:29 GMT

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

Re: Left join not working

Gil_ — Thu, 20 Apr 2017 19:44:33 GMT

Thank you both for the assistance