topic Re: PROC SQL - Include zeros in totals in New SAS User

PROC SQL - Include zeros in totals

osbornejo — Tue, 18 Jan 2022 21:54:20 GMT

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

Re: PROC SQL - Include zeros in totals

acordes — Tue, 18 Jan 2022 22:13:17 GMT

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;

Re: PROC SQL - Include zeros in totals

Reeza — Tue, 18 Jan 2022 22:37:57 GMT

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

Re: PROC SQL - Include zeros in totals

acordes — Tue, 18 Jan 2022 22:44:56 GMT

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;

Re: PROC SQL - Include zeros in totals

Ksharp — Wed, 19 Jan 2022 12:50:16 GMT

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;