topic Re: Help with table displaying dates with zero counts in SAS Programming

Help with table displaying dates with zero counts

Krysia24 — Wed, 15 Apr 2020 23:12:42 GMT

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 ;

Re: Help with table displaying dates with zero counts

Reeza — Thu, 16 Apr 2020 01:58:01 GMT

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;

Re: Help with table displaying dates with zero counts

andreas_lds — Thu, 16 Apr 2020 05:02:27 GMT

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.

Re: Help with table displaying dates with zero counts

kelxxx — Thu, 16 Apr 2020 15:22:34 GMT

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

Re: Help with table displaying dates with zero counts

Krysia24 — Thu, 16 Apr 2020 20:33:40 GMT

Thank you! This worked perfectly!