topic Re: COALESCE function in PROC SQL question in SAS Programming

COALESCE function in PROC SQL question

greg6363 — Wed, 30 Sep 2020 22:46:27 GMT

I am trying to use the COALESCE function with PROC SQL in order to output zero values for the GROUP BY variable categories in my data set. Here is how I constructed the code string:

coalesce (count(distinct AccountNumber),0) as total_accts format comma14.,
coalesce (sum(balance),0) as total_bal format dollar14.2

Unfortunately, I still receive no output for any GROUP BY variables that produce zero values and no error message is produced in the log. What am I doing wrong? Any assistance would be greatly appreciated for this programming issue. Thanks.

Re: COALESCE function in PROC SQL question

SASKiwi — Wed, 30 Sep 2020 23:23:48 GMT

If a particular combination of GROUP BY variable categories does not exist in your input data no row will be produced in your output data for that combination so COALESCE will not fix that.

If you want an output row for every combination of GROUP BY variable category then you will have to create a table of those first and then UNION that with your input table:

proc sql;
  create table want as
  select CategoryVar
            ,count(distinct (AccountNumber)) as Count
  from Have
  group by CategoryVar
  having Count > 0
  union
  select CategoryVar
          ,0
  from HaveWithAllCategories
  ;
quit;

Re: COALESCE function in PROC SQL question

ChrisNZ — Thu, 01 Oct 2020 01:38:06 GMT

Your SQL query will not create data that does not exist.

To create all possible crossings, you can replace and expand the source table of your query:

...

from (select unique VAR1 from TABLE) cart1

cross join

(select unique VAR2 from TABLE) cart2

natural left join

TABLE data

group by cart1.VAR1, cart2.VAR2;

Re: COALESCE function in PROC SQL question

ChrisNZ — Thu, 01 Oct 2020 03:50:02 GMT

There seems to be something not quite right with SAS and you may need to add a keep option:

from (select unique VAR1 from TABLE(keep=VAR1)) cart1

cross join

(select unique VAR2 from TABLE(keep=VAR2)) cart2

natural left join

TABLE data

group by cart1.VAR1, cart2.VAR2;

Re: COALESCE function in PROC SQL question

PGStats — Thu, 01 Oct 2020 14:05:31 GMT

natural joins can be tricky, I prefer a more explicit join:

proc sql;
select 
    a.sex,
    b.age,
    count(weight) as n,
    mean(weight) as meamWeight
from
    (select unique sex from sashelp.class) as a
    cross join
    (select unique age from sashelp.class) as b
    left join 
    sashelp.class as c on a.sex=c.sex and b.age=c.age
group by a.sex, b.age;
quit;

Re: COALESCE function in PROC SQL question

ChrisNZ — Thu, 01 Oct 2020 05:10:21 GMT

Agree, I avoid natural joins, but here there is no room for error or ambiguity.

Re: COALESCE function in PROC SQL question

ChrisNZ — Fri, 02 Oct 2020 00:59:50 GMT

A defect has been created for the inability to run the query without an uneeded modification such as adding keep=.

@art297 Another defect! 🙂