topic Data count per minute in SAS Procedures

Data count per minute

Osebeyo — Tue, 05 Jul 2011 15:35:34 GMT

Hello,

I need a help urgently with a large dataset that I am working on, I will like to be able to count number of events per minute, see below for an hypothetical data set and the result I am looking to get.

data temp;

input obs datetime : datetime id ;

format datetime datetime.;

datalines;

1 6/8/2011:00:00:30 1

2 6/8/2011:00:00:15 1

3 6/8/2011:00:01:00 1

4 6/8/2011:00:01:50 1

5 6/8/2011:00:01:10 1

6 6/8/2011:00:01:30 1

7 6/8/2011:00:02:00 1

8 6/8/2011:00:02:44 1

9 6/8/2011:00:00:17 1

10 6/8/2011:00:02:10 1

11 6/8/2011:00:03:21 1

12 6/8/2011:00:04:00 1

13 6/8/2011:00:03:32 1

;

run;

To count the number of activity within each minute, which means for a whole day I am expecting to have 1,440 rows of data

e.g

less or equal to 6/8/2011:00:01:00 4

less or equal to 6/8/2011:00:02:00 greater than 6/8/2011:00:01:00 4

less or equal to 6/8/2011:00:03:00 greater than 6/8/2011:00:02:00 2

less or equal to 6/8/2011:00:04:00 greater than 6/8/2011:00:03:00 3

Any help will be most appreciated.

Adeboye

Re: Data count per minute

data_null__ — Tue, 05 Jul 2011 16:15:14 GMT

I would make the intervals go from 0-59 seconds, and use the INTNX function to create the minute grouping variable.

data temp;

input obs datetime : mdyampm. id;

minute = intnx('dtminute1',datetime,0,'b');

format datetime minute datetime.;

datalines;

0 6/8/2011:00:00:00 1

1 6/8/2011:00:00:30 1

2 6/8/2011:00:00:59 1

3 6/8/2011:00:01:00 1

4 6/8/2011:00:01:50 1

5 6/8/2011:00:01:10 1

6 6/8/2011:00:01:30 1

7 6/8/2011:00:02:00 1

8 6/8/2011:00:02:44 1

9 6/8/2011:00:00:17 1

10 6/8/2011:00:02:10 1

11 6/8/2011:00:03:21 1

12 6/8/2011:00:04:00 1

13 6/8/2011:00:03:32 1

;;;;

run;

proc print;

run;

Obs obs datetime id minute

1 0 08JUN11:00:00:00 1 08JUN11:00:00:00

2 1 08JUN11:00:00:30 1 08JUN11:00:00:00

3 2 08JUN11:00:00:59 1 08JUN11:00:00:00

4 3 08JUN11:00:01:00 1 08JUN11:00:01:00

5 4 08JUN11:00:01:50 1 08JUN11:00:01:00

6 5 08JUN11:00:01:10 1 08JUN11:00:01:00

7 6 08JUN11:00:01:30 1 08JUN11:00:01:00

8 7 08JUN11:00:02:00 1 08JUN11:00:02:00

9 8 08JUN11:00:02:44 1 08JUN11:00:02:00

10 9 08JUN11:00:00:17 1 08JUN11:00:00:00

11 10 08JUN11:00:02:10 1 08JUN11:00:02:00

12 11 08JUN11:00:03:21 1 08JUN11:00:03:00

13 12 08JUN11:00:04:00 1 08JUN11:00:04:00

14 13 08JUN11:00:03:32 1 08JUN11:00:03:00

Data count per minute

Osebeyo — Tue, 05 Jul 2011 16:28:39 GMT

Thanks for this, it looks a lot neater. However, I will like to know if it is possible to group your output by 'minute'

so i can have something like this

minute count

08JUN11:00:00:00 4

08JUN11:00:01:00 4

08JUN11:00:02:00 3

08JUN11:00:03:00 2

08JUN11:00:04:00 1

Thanks for your help

Data count per minute

DLing — Tue, 05 Jul 2011 17:19:25 GMT

Two options:

1) Use data step logic using lag() and increment counter yourself; or

2) Replace proc print with proc means should give you the summary:

proc means data=<whatever> missing nway;

class minute;

output out=summary(drop=_type_ rename=(_freq_=count));

run;

by default you'll get a listing. use the "noprint" option to suppress listing on proc means.

A dataset called "summary" will be created by the output statement. This is probably what you want for further analysis.

Data count per minute

data_null__ — Tue, 05 Jul 2011 23:37:24 GMT

I though you would know that. You can also create the grouping without creating a new variable using datetime14. format.

data temp;

input obs datetime : mdyampm. id;

minute = intnx('dtminute1',datetime,0,'b');

format datetime minute datetime.;

datalines;

0 6/8/2011:00:00:00 1

1 6/8/2011:00:00:30 1

2 6/8/2011:00:00:59 1

3 6/8/2011:00:01:00 1

4 6/8/2011:00:01:50 1

5 6/8/2011:00:01:10 1

6 6/8/2011:00:01:30 1

7 6/8/2011:00:02:00 1

8 6/8/2011:00:02:44 1

9 6/8/2011:00:00:17 1

10 6/8/2011:00:02:10 1

11 6/8/2011:00:03:21 1

12 6/8/2011:00:04:00 1

13 6/8/2011:00:03:32 1

;;;;

run;

proc print;

run;

proc summary data=temp nway;

class id datetime;

format datetime datetime14.;

output out=sum1;

run;

proc summary data=temp nway;

class id minute;

output out=sum2;

run;

proc compare base=sum1 compare=sum2;

run;

Re: Data count per minute

dhana — Wed, 06 Jul 2011 10:07:59 GMT

Hi,

I tried couple of solutions given here, but I didn't get proper output. The below one is a tested solution.

DATA EVENT(KEEP=OBS ID XDATE MINUTE);

INPUT OBS XTEXT :$19. ID;

XDATE=INPUT(XTEXT,ANYDTDTM30.);

MINUTE=INTNX('MINUTE',XDATE,0);

FORMAT XDATE MINUTE DATETIME.;

DATALINES;

...

;

RUN;

PROC SORT DATA = EVENT;

BY MINUTE;

RUN;

PROC FREQ DATA = EVENT;

TABLES MINUTE;

RUN;

Hope this helps...!

Thanks

Dhanasekaran R

Re: Data count per minute

Ksharp — Fri, 08 Jul 2011 06:24:56 GMT

How about:

options datestyle=mdy;
data temp;
input obs datetime : anydtdtm20. id ;
minute=minute(datetime);
format datetime datetime.;
datalines;
1  6/8/2011:00:00:30 1
2  6/8/2011:00:00:15 1
3  6/8/2011:00:01:00 1
4  6/8/2011:00:01:50 1
5  6/8/2011:00:01:10 1
6  6/8/2011:00:01:30 1
7  6/8/2011:00:02:00 1
8  6/8/2011:00:02:44 1
9  6/8/2011:00:00:17 1
10  6/8/2011:00:02:10 1
11  6/8/2011:00:03:21 1
12  6/8/2011:00:04:00 1
;
run;
proc sort data=temp;
by minute;
run;
data want; 
 set temp;
 by minute;
 retain count;
 if first.minute then count=0;
 count+1;
 if last.minute then output;
run;

Ksharp