BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gil_
Quartz | Level 8
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 ...
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

12 REPLIES 12
Gil_
Quartz | Level 8
 
art297
Opal | Level 21

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

 

 

 

 

 

Gil_
Quartz | Level 8
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;
art297
Opal | Level 21

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

 

Gil_
Quartz | Level 8
I change the name of tables to not be confusing. Thanks art
Gil_
Quartz | Level 8
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
PGStats
Opal | Level 21

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
Gil_
Quartz | Level 8
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?
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

PGStats
Opal | Level 21

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
Gil_
Quartz | Level 8
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
Gil_
Quartz | Level 8
Thank you both for the assistance

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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