COALESCE function in PROC SQL question

[greg6363]

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.

[SASKiwi]

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;

[ChrisNZ]

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;

[ChrisNZ]

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;

[PGStats]

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;

[ChrisNZ]

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

[ChrisNZ]

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

@art297 Another defect! 🙂