topic Counts over a 24hr period in SAS Programming

Counts over a 24hr period

bbarn — Wed, 02 Jun 2021 21:05:35 GMT

Any help would be appreciated.

I am looking to pull a weeks worth of data and get the rolling number of devices to accountsID's over a 24hr period and sort them with the highest count at the top. Hope that makes sense.

%let start = '01apr2021 00:00:00'dt;
%let end = '07apr2021 00:00:00'dt;
%let status = '14may2021 00:00:00'dt;

/* Base Data Query */
proc sql;
create table BaseQuery as
select ReportDate,
AccountID,
DeviceNumber,
datepart(CreateDate) format date9. as date,
timepart(CreateDate) format time8. as time,
TrackingNumber,

from my.database

where ReportDate between &start. and &end.

;
quit;

Re: Counts over a 24hr period

mkeintz — Thu, 03 Jun 2021 02:26:33 GMT

What do you mean by "rolling number of devices to accountsID's over a 24hr period"? Do you mean for every hour (or every minute, or every day) between &start and &end, you want the number of device records for the preceding 24 hours? If so, do you want this rolling number for each id, or accumulated over all id's?

Re: Counts over a 24hr period

bbarn — Fri, 11 Jun 2021 23:32:44 GMT

Do you mean for every hour between &start and &end, you want the number of device records for the preceding 24 hours? yes

If so, do you want this rolling number for each id, or accumulated over all id's? Each ID