topic Re: Proc SQL Replace Null With Another Field Value in New SAS User

Proc SQL Replace Null With Another Field Value

eaherbst — Thu, 04 Apr 2019 14:29:52 GMT

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.

Re: Proc SQL Replace Null With Another Field Value

PaigeMiller — Thu, 04 Apr 2019 14:36:40 GMT

@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.

Re: Proc SQL Replace Null With Another Field Value

eaherbst — Thu, 04 Apr 2019 14:44:59 GMT

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.

Re: Proc SQL Replace Null With Another Field Value

PaigeMiller — Thu, 04 Apr 2019 14:50:33 GMT

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

Re: Proc SQL Replace Null With Another Field Value

Reeza — Thu, 04 Apr 2019 14:52:34 GMT

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.

Re: Proc SQL Replace Null With Another Field Value

eaherbst — Thu, 04 Apr 2019 15:09:24 GMT

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