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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.