Proc SQL Replace Null With Another Field Value

[eaherbst]

Hi all,

I am trying to select the min of a date and then if it is null I would like to replace it with the value of another date field:

Select

a.Acct

,a.EntryDte

,case b.PostDte when null then a.EntryDte else min(b.PostDte) end as PostDate

from tableA A

left outer join tableB B

on A.Nbr = B.Nbr

group by a.Acct, a.EntryDte

while it is grouping the way I want (the b.PostDte has several values per account number), my desired field PostDate is still showing up as null and not the EntryDte (01APR2019) e.g.:

acct   EntryDte    PostDate

123    01APR2019    null

456    01APR2019    14MAR2019

Any insight is appreciated!

I have tried this in SAS EG with a proc SQL as well as an SQL program without any luck.

[PaigeMiller]

---

@eaherbst wrote:

Select

a.Acct

,a.EntryDte

,case b.PostDte when null then a.EntryDte else min(b.PostDte) end as PostDate

from tableA A

left outer join tableB B

on A.Nbr = B.Nbr

group by a.Acct, a.EntryDte

while it is grouping the way I want (the b.PostDte has several values per account number), my desired field PostDate is still showing up as null and not the EntryDte (01APR2019) e.g.:

acct   EntryDte    PostDate

123    01APR2019    null

456    01APR2019    14MAR2019

---

Confusing, to be sure. In the last table, it shows the four letters "null" (without the quotes). Did you type it in that way, or is that what actually is produced? If that is what actually is produced, is that a format, or is the variable PostDate a character variable?

I'm thinking you have a format that produces the letter null, but it would be nice to know for sure.

I also point out that if PostDate is missing, then min(b.PostDte) is also a missing.

[eaherbst]

I typed in null, it actually shows up as '.' in SAS so it is not a formatting issue. I understand min(PostDte) would be missing if PostDte is missing, regardless, I would like PostDte (or min(PostDte) to show up as EntryDte if it is missing.

[PaigeMiller]

Select a.Acct
,a.EntryDte
,case b.PostDte when . then a.EntryDte else min(b.PostDte) end as PostDate

[Reeza · Accepted solution]

COALESCE() is a function to replace missing with a value.
Try the following and if it doesn't work, consider first summarizing/joining and then filling in the missing data.

select ...,
min(b.postdte) as min_date,
min(EntryDte) as min_entry,
coalesce(calculated min_date, calculated min_entry) as POstDate

The problem here is you're using MINDATE which is a summary statistics of a column and are also referencing Entry date, which you're not using as an aggregate statistics. If you know it's the same across the group you're fine.

[eaherbst]

Thank you for the suggestion of using coalesce! I have gotten the desired results with the following code:

Select

a.Acct

,a.EntryDte

,coalesce(min(b.PostDte), min(a.EntryDte)) as PostDate