BookmarkSubscribeRSS Feed
Stretlow
Obsidian | Level 7

Hi There.

 

I have a data set that contains a datetime value that ive created from separate date and time variables and a unique identifier "IP" which is character.

 

What i cant quite figure out is I want to show a count of the same "IP" in a 60 minute window.

 

so for example 

 

IP           Count

123456   6

654321   1

233242   7

 

Etc.

 

Any help as always is appreciated.

 

Stret

9 REPLIES 9
Stretlow
Obsidian | Level 7

@Kurt_Bremser wrote:

Please post an example of your data (see my footnotes for how to post data) from which your expected result is to be derived.


Hi there i've attached a sample excel sheet

 

Apologies.

Kurt_Bremser
Super User

Excel spreadsheets are not SAS datasets, and they cannot be used to reliably represent SAS datasets (no column attributes etc). Furthermore, they can be used to transport malware, so many corporate institutions block their download.

Please follow the hints contained in my footnotes. It is NOT rocket science.

 

Finally, I can perfectly read SAS code on my tablet without need for any special app.

Stretlow
Obsidian | Level 7

@Kurt_Bremser wrote:

Excel spreadsheets are not SAS datasets, and they cannot be used to reliably represent SAS datasets (no column attributes etc). Furthermore, they can be used to transport malware, so many corporate institutions block their download.

Please follow the hints contained in my footnotes. It is NOT rocket science.

 

Finally, I can perfectly read SAS code on my tablet without need for any special app.


Hello,

 

I'm not sure the rudeness was warranted however I have looked at the three links in your signature and while it may not be rocket science i do not see the easy way to post the data. 

 

This is obviously beyond my level of understanding, which I make no apologies for, I am just looking for a little assistance on something that i don't know.

 

Kind Regards

 

Stret

RW9
Diamond | Level 26 RW9
Diamond | Level 26

"I have a data set that contains a datetime value that ive created from separate date and time variables and a unique identifier "IP""

Therefore:

data have;
  input ip dt e8601dt.;
new_date datalines; 123456 2017-08-21T12:01:00 123456 2017-08-26T11:00:00 ... run;

This means we can run it on our machine to get data which looks like yours.  Without this we are guessing, and hence can't write tested code which accurately answers your question.  SAS is a data driven programming language, so the data is very important to any coding.

 

In terms of your question, step 1 would be to create a variable based on what your window is.  Maybe:

data want;
  set have;
  retain base group;
  by ip;
  if first.ip=1 then do;
    group=1;
    base=dt;
  end;
  else do;
    if dt-base > ... then do;
      group=group+1;
      base=dt;
    end;
  end;
run;
  

So this keeps a "base" time, and changes group and base only when something goes outside the 60 minute window - how you do that check is up to you.

Stretlow
Obsidian | Level 7

@RW9 wrote:

"I have a data set that contains a datetime value that ive created from separate date and time variables and a unique identifier "IP""

Therefore:

data have;
  input ip dt e8601dt.;
new_date datalines; 123456 2017-08-21T12:01:00 123456 2017-08-26T11:00:00 ... run;

This means we can run it on our machine to get data which looks like yours.  Without this we are guessing, and hence can't write tested code which accurately answers your question.  SAS is a data driven programming language, so the data is very important to any coding.

 

In terms of your question, step 1 would be to create a variable based on what your window is.  Maybe:

data want;
  set have;
  retain base group;
  by ip;
  if first.ip=1 then do;
    group=1;
    base=dt;
  end;
  else do;
    if dt-base > ... then do;
      group=group+1;
      base=dt;
    end;
  end;
run;
  

So this keeps a "base" time, and changes group and base only when something goes outside the 60 minute window - how you do that check is up to you.


 

 

i understand completely I wasn't myself trying to be rude I just didn't see any obvious instructions on how to do it for an amateur and don't appreciate the sarcastic comments. 

 

I really do appreciate the help.

 

I'm more than happy to though

Stretlow
Obsidian | Level 7

@RW9 wrote:

"I have a data set that contains a datetime value that ive created from separate date and time variables and a unique identifier "IP""

Therefore:

data have;
  input ip dt e8601dt.;
new_date datalines; 123456 2017-08-21T12:01:00 123456 2017-08-26T11:00:00 ... run;

This means we can run it on our machine to get data which looks like yours.  Without this we are guessing, and hence can't write tested code which accurately answers your question.  SAS is a data driven programming language, so the data is very important to any coding.

 

In terms of your question, step 1 would be to create a variable based on what your window is.  Maybe:

data want;
  set have;
  retain base group;
  by ip;
  if first.ip=1 then do;
    group=1;
    base=dt;
  end;
  else do;
    if dt-base > ... then do;
      group=group+1;
      base=dt;
    end;
  end;
run;
  

So this keeps a "base" time, and changes group and base only when something goes outside the 60 minute window - how you do that check is up to you.


 

Hi Again.

 

Ok i think I see what you mean now, apologies for my knowledge on this

 

so if you run this code 

 

data have;
input IP DateTime DATETIME18.;

datalines;
123456 31MAR18:23:58:52
123456 31MAR18:23:57:19
123456 31MAR18:23:55:46
654321 31MAR18:23:44:18
654321 31MAR18:23:35:11


run;

 

then it will give you some sample data that is in the same format as mine, is that was you require?

 

so the output of the count would be 

 

IP           Count in 60 mins

123456             3

654321             2

 

 

Stret

Kurt_Bremser
Super User

A simple and short approach might look like this:

proc sql;
create table want as
select ip, datepart(datetime) as date, hour(timepart(datetime)) as hour, count(*) as count
from have
group by ip, calculated date, calculated hour;
quit;

If you need a different definition of "hour" (the sixty-minute window), you might have to create "hour" in a preliminary (data) step.

SuryaKiran
Meteorite | Level 14

When you say 60 Min, Which 60 min do you want to consider? Is  it from the first timestamp value, then look at this code:

 

data have;
Format DateTime DATETIME18.;
input IP DateTime DATETIME18.;
datalines;
123456 31MAR18:21:58:52
123456 31MAR18:21:57:19
123456 31MAR18:21:55:46
123456 31MAR18:23:55:46
123456 31MAR18:23:58:46
654321 31MAR18:23:44:18
654321 31MAR18:23:35:11
;
run;
/* Sort the data first */
PROC SORT DATA=have;
by IP DateTime;
run;

DATA WANT;
Format lag_Date DATETIME18.;
Retain Time;
set have;
by  IP DateTime;
lag_Date=LAG(DateTime); /* Previous DateTime to Compare with current DateTime */
If first.IP then Do;
		lag_Date=DateTime;
		Time=0;
		Count=1;
		end;
else Time+INTCK('SECONDS',lag_Date,DateTime); /* Number of seconds from previous Datetime to current Datetime */
If time<=60*60 and time^=0 then count+1;
If last.IP;
Keep IP Count;
run;

else, if you want to count every hourly then use HOUR() for hour and sort the data  by IP DATE and Hour and use FIRST. for these by variables for count.

data want2;
set have;
hour=hour(DateTime);
Date=datepart(Datetime);
run;

Proc sort data=want2;
by IP Date Hour;
run;
data final;
set want2;
by IP Date Hour;
if first.Hour then count=1;
else count+1;
if last.hour;
keep IP Hour Count;
run;
Thanks,
Suryakiran

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1918 views
  • 0 likes
  • 4 in conversation