How can I round time using SAS?

[aminkarimid]

Hello everybody

I have a little problem and appreciate if anyone could help me.

I have intraday data (Trading data of each second):

Sample of my data:

TRD_EVENT_DT	TRD_EVENT_TM	TRD_EVENT_MS	TRD_STCK_ID	TRD_STCK_CD	TRD_INSTR_NAME	TRD_STCK_GRP_ID_CD	TRD_PR	TRD_TUROVR	TRD_TRA_NR
3/24/2008	12:19:35		IRB5IKCO8751	IKCQ1	187191011	N2	1	100	122197901
3/24/2008	12:28:01		IRO1ALBZ0001	ALBZ1	والبر1	N1	1537	10000	122198037
3/24/2008	13:13:44		IRO1ALBZ0001	ALBZ1	والبر1	N1	1567	10	9999198173
3/24/2008	12:20:38		IRO1AZAB0001	AZAB1	فاذر1	N2	683	10000	122197911
3/24/2008	13:13:44		IRO1AZAB0001	AZAB1	فاذر1	N2	695	10	9999198167
3/24/2008	09:14:57		IRO1BALI0001	BALI1	وبوعلي1	N2	850	9260	122197242
3/24/2008	09:15:06		IRO1BALI0001	BALI1	وبوعلي1	N2	850	2000	122197245
3/24/2008	09:15:14		IRO1BALI0001	BALI1	وبوعلي1	N2	850	10000	122197247
3/24/2008	09:15:24		IRO1BALI0001	BALI1	وبوعلي1	N2	850	6000	122197249
3/24/2008	09:29:27		IRO1BALI0001	BALI1	وبوعلي1	N2	850	10000	122197296
3/24/2008	12:28:00		IRO1BALI0001	BALI1	وبوعلي1	N2	850	10000	122198036
3/24/2008	12:28:07		IRO1BALI0001	BALI1	وبوعلي1	N2	850	10000	122198041
3/24/2008	13:13:44		IRO1BALI0001	BALI1	وبوعلي1	N2	865	10	9999198133

Explanation of headers of some columns:

[TRD_EVENT_DT] Month / Day / Year
,[TRD_EVENT_TM] Time: hh:mm:ss
,[TRD_EVENT_MS] Millisecond
,[TRD_STCK_ID] 
,[TRD_STCK_CD] 
,[TRD_INSTR_NAME] 
,[TRD_STCK_GRP_ID_CD] 
,[TRD_PR] Stock Price
,[TRD_TUROVR] Turnover
,[TRD_TRA_NR] 

I want to categorize this data in half an hour periods:

First half an hour: 9:00 << Volume_1 < 9:30

Second half an hour: 9:30 << Volume_2 < 10:00

Third half an hour: 10:00 << Volume_3 < 10:30

Fourth half an hour: 10:30 << Volume_4 < 11

Fifth half an hour: 11:00 << Volume_5 < 11:30

Sixth half an hour: 11:30 << Volume_6 << 12

What I'm trying to do is basically round the time part to the nearest 30 minute. 

My question is how can I do rounding data using SAS.

Thanks for your attention.

[ballardw]

First get a datetime variable.

It would help if your example data actually included some millisecond values as we do not know if you have values like:

1

.001

or something else.

data example;
   informat TRD_EVENT_DT mmddyy10. TRD_EVENT_TM time8. TRD_EVENT_MS best5. ;
   format TRD_EVENT_DT mmddyy10. TRD_EVENT_TM time12.3;
   input TRD_EVENT_DT TRD_EVENT_TM TRD_EVENT_MS ;
   /* assumes milliseconds are integers where 1= 1ms
      if the values are 0.001 then the division by 1000 is not needed*/
   TRD_EVENT_TM = sum(TRD_EVENT_TM , TRD_EVENT_MS/1000);
   TRD_dttme= dhms(TRD_EVENT_DT,0,0,TRD_EVENT_TM);
   format TRD_dttme TRD_dttme_rnd datetime20.3  ;
   TRD_dttme_rnd = round(TRD_dttme,1800);
   
datalines;
3/24/2008 12:19:35   25
3/24/2008 12:28:01   .
3/24/2008 13:13:44   .
3/24/2008 12:20:38   .
3/24/2008 13:13:44   .
3/24/2008 09:14:57   1
3/24/2008 09:15:06   .
3/24/2008 09:15:14   .
3/24/2008 09:15:24   .
3/24/2008 09:29:27   100
3/24/2008 12:28:00   .
3/24/2008 12:28:07   .
3/24/2008 13:13:44   .
;
run;

If your existing variables are not date and time types then you need to covert.

Also post data in the form of a data step. This is about the 6th topic with the same data and we still cannot tell if your data is character or SAS date and time valued.

[aminkarimid]

The SAS System
The CONTENTS Procedure

Data Set Name WORK.SAMPLEDATA87 Observations 100
Member Type DATA Variables 10
Engine V9 Indexes 0
Created 05/13/2017 01:10:26 Observation Length 144
Last Modified 05/13/2017 01:10:26 Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_64
Encoding wlatin1 Western (Windows)

Engine/Host Dependent Information
Data Set Page Size 65536
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 454
Obs in First Data Page 100
Number of Data Set Repairs 0
ExtendObsCounter YES
Filename C:\Users\amin\AppData\Local\Temp\SAS Temporary
Files\_TD16616_DESKTOP-623A1UK_\sampledata87.sas7bdat
Release Created 9.0401M3
Host Created X64_8PRO

Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat Label
1 TRD_EVENT_DT Num 8 MMDDYY10. TRD_EVENT_DT
3 TRD_EVENT_MS Char 15 $15. $15. TRD_EVENT_MS
2 TRD_EVENT_TM Char 16 $16. $16. TRD_EVENT_TM
6 TRD_INSTR_NAME Char 15 $15. $15. TRD_INSTR_NAME
8 TRD_PR Char 15 $15. $15. TRD_PR
5 TRD_STCK_CD Char 16 $16. $16. TRD_STCK_CD
7 TRD_STCK_GRP_ID_CD Char 16 $16. $16. TRD_STCK_GRP_ID_CD
4 TRD_STCK_ID Char 15 $15. $15. TRD_STCK_ID
10 TRD_TRA_NR Char 13 $13. $13. TRD_TRA_NR
9 TRD_TUROVR Char 14 $14. $14. TRD_TUROVR

Thank you;
I need help how can I round all data time.
What is datalines statement & I want to round all time.
##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Tom]

Your sample data does not have any time variables.

Are some of those character variables supposed to be time variables? 

You will need to convert them first.

/* TRD_EVENT_TM Char 16 */
trd_event_time = input(trd_event_tm,time16.);
trd_event_30min = round(trd_event_time,'00:30't);

[Rick_SAS]

SAS time values are measured in seconds since midnight. There are 1800 seconds in 30 minutes, so round to the nearest 1800. You can then use the rounded value in a BY statement or CLASS statement to aggregate values that occur in each half-hour period:

data A;
informat t time10.;
format t r time14.;
input t;
r = round(t, 1800);
datalines;
00:14:12
00:16:12
09:01:23
09:46:32
15:59:45
;
proc print; run;

[aminkarimid]

Hello; Thank you for your attention;

Please help me how can I round all time.

[aminkarimid · Accepted solution]

Thank you; Finally I find a solution!

*Importing Data;
PROC IMPORT DATAFILE= 'D:\In process\Thesis\Intraday Pattern of Volatility\SampleData\Table87.xls'
OUT= SampleData87
DBMS= XLS;
RUN;

PROC CONTENTS DATA= Sampledata87;
RUN;
*Step1: Rounding Time;
DATA sampledata;
SET sampledata87;
TRD_EVENT_TIME = INPUT(TRD_EVENT_TM,time16.);
TRD_EVENT_ROUNDED = round(TRD_EVENT_TIME,'00:30't);
TRD_EVENT_ROUFOR=put(TRD_EVENT_ROUNDED,hhmm.);

PROC PRINT; RUN;