Help with table displaying dates with zero counts

[Krysia24]

Hi all, 

I'd like to create a table where the count of something is displayed by day. I am interested in also displaying the days where the count was zero. Here's what I have, which obviously only yields dates where count for that day was >=1. 

PROC SQL ;
CREATE TABLE graph_data as
SELECT
dtevent, 
count(*) as totalnum
FROM latestcounts
GROUP BY dtevent
ORDER By dtevent ;
QUIT ;

[Reeza]

If the data isn't there you have to create it. For TIMESERIES data you can use PROC TIMESERIES to fill in the dates, or depending on the complexity you can define a dataset with all the possible dates and merge on that or develop a CLASSDATA that can be used with PROC TABULATE.

I'd probably lean towards PROC TIMESERIES here.

/*1 - Create data set with missing data*/
data ibm;
set sashelp.stocks;
where stock='IBM';

if month(date)=7 then
delete;
run;

proc sort data=ibm;
by date;
run;

/*2 - Fill in missing values - you need to know the start and end of your series*/
proc timeseries data=ibm out=ibm_no_missing;
id date interval=month start='01Aug1986'd end='01Dec2005'd;
var open;
run;

[andreas_lds]

Unfortunately proc timeseries writes warnings to the log - which is a great idea in other scenarios, but because we want the gaps to be filled i would not expect warnings telling me that the procedure did what i wanted it to do. After a quick glance in the docs i was not able to find an option to disable the warnings, but perhaps i have overlooked something.

[kelxxx · Accepted solution]

proc sql noprint;
select min(dtevent) , max(dtevent) into :sdate,:edate from latestcounts;
quit;

data dateev;
do dtevent=&sdate to &edate;
output;
end;
run;

PROC SQL ;
CREATE TABLE graph_data as
SELECT
l.dtevent,case when r.totalnum=. then 0 else r.totalnum end as totalnum 
from dateev as L left join 
(SELECT
dtevent, count(*) as totalnum
FROM latestcounts
GROUP BY dtevent) as R
on L.dtevent = R.dtevent
;
QUIT ;

an other solution If you don t have proc timeseries

1- attribute min and max dtevent into 2 macro variable

2- create a serie of date between min & max date

3- left join with proc sql

[Krysia24]

Thank you! This worked perfectly!