BookmarkSubscribeRSS Feed
bbarn
Fluorite | Level 6

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;

2 REPLIES 2
mkeintz
PROC Star

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?

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
bbarn
Fluorite | Level 6

 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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 535 views
  • 0 likes
  • 2 in conversation