PROC SQL - Include zeros in totals

[osbornejo]

I have a large table (HAVE) with variables Units, Code, and Year.  I'm trying to create a summary of counts by code and year:

proc sql;
    create table WANT as
    select Code, Year, sum(Units) as Total
    from HAVE
    group by Code, Year
quit;

This works, except there are some combinations of Code and Year that have no Units, which means the resulting table doesn't include those combinations at all.  I want to include all combinations, and show 0 in the Total column when there are no Units.  I've tried playing with the coalesce() function [as coalesce(sum(Units),0) and as sum(coalesce(Units,0))] but it hasn't given me the desired result so far.

Current result:

Code    Year    Total

A           2020   1

B           2020   3

B           2021   7

Desired result:

Code    Year    Total

A           2020   1

B           2020   3

A           2021   0

B           2021   7

[acordes]

data elect;
set SASHELP.ELECTRIC;
if mod(_n_,6)=0 then call missing (revenue);
run;

proc means data=elect nway completetypes missing;
class Customer 'Year'n;
var Revenue;
output out=sumy sum= / autoname;
run;

[acordes]

It should be possible as well

data elect;
set SASHELP.ELECTRIC;
if mod(_n_,6) ne 0 then output;
run;

proc sql;
create table have2 as 
select c.customer, c.'year'n, case when sum(revenue) in (. 0) then 0 else sum(revenue) end as revenue_sum format = dollar10.
from elect d 
right join 
(
select a.customer, b.'year'n 
from (select distinct customer from elect ) a cross join (select distinct 'year'n from elect) b
) c 
on c.customer||put(c.'year'n, 4.)=d.customer||put(d.'year'n,4.)
group by d.customer, d.'year'n
order by c.customer, c.'year'n 
;
quit;

[Reeza · Accepted solution]

Use PROC FREQ instead.

proc freq data=have noprint;
table code*year / out=want SPARSE;
weight units;
run;

---

@osbornejo wrote:

I have a large table (HAVE) with variables Units, Code, and Year.  I'm trying to create a summary of counts by code and year:

proc sql;
    create table WANT as
    select Code, Year, sum(Units) as Total
    from HAVE
    group by Code, Year
quit;

This works, except there are some combinations of Code and Year that have no Units, which means the resulting table doesn't include those combinations at all.  I want to include all combinations, and show 0 in the Total column when there are no Units.  I've tried playing with the coalesce() function [as coalesce(sum(Units),0) and as sum(coalesce(Units,0))] but it hasn't given me the desired result so far.

Current result:

Code    Year    Total

A           2020   1

B           2020   3

B           2021   7

Desired result:

Code    Year    Total

A           2020   1

B           2020   3

A           2021   0

B           2021   7

---

[Ksharp]

data have;
 set sashelp.class;
rename name=code age=year weight=units;
run;


proc sql;
    create table WANT as
select a.*,coalesce(b.total,0) as total
from (select * from (select distinct code from have),(select distinct year from have)) as a
natural left join
(
    select Code, Year, sum(Units) as Total
    from HAVE
    group by Code, Year 
) as b;
quit;