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
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
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;
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.