topic Outer join- force missing values to not match? in SAS Programming

Outer join- force missing values to not match?

erwint — Tue, 22 Aug 2023 18:21:11 GMT

Hi!

I am working with two tables that have missing values for IDs. I am doing an outer join for the two tables. I want to make sure rows are only joined if the IDs are NOT null. I still would like these printed, just not joined. Can I do this in one SQL step?

Example: if I join these tables:

ID	Amount
123	45
	48
	24

ID	Tax
123	23
	4

I want this output:

ID	Amount	Tax
123	45	23
	48	.
	24	.
	.	4

Re: Outer join- force missing values to not match?

ballardw — Tue, 22 Aug 2023 19:44:56 GMT

Are any of the ID values other than missing repeated?

Do you expect the order of the Amount variable to remain the same in relation to the ID from the first set?

(Hint: provide some sort of name for the data sets so we can be sure which one anyone is discussing at a given point). Better: provide working data step code to provide example sets to write code against.

It is kind of hard to see what actual rules may be involved when only one matching ID is provided.

Re: Outer join- force missing values to not match?

Tom — Tue, 22 Aug 2023 20:13:14 GMT

So SAS equality tests will match missing values (of the same type of missing value). So . will match . but not match .A or any actual number.

If you want exclude those then just add an additional condition. Just test if one of the two values is missing should be enough.

select *
from A
  left join B
  on a.id = b.id and not missing(a.id) 
;