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;
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
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
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.
Given the two test files I posted earlier, show us which records you expect to get and why.
Art, CEO, AnalystFinder.com
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;
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.
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.