topic How can I round time using SAS? in SAS Programming

How can I round time using SAS?

aminkarimid — Fri, 12 May 2017 06:34:03 GMT

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.

Re: How can I round time using SAS?

ballardw — Fri, 12 May 2017 14:29:30 GMT

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:

.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.

Re: How can I round time using SAS?

Rick_SAS — Fri, 12 May 2017 20:18:26 GMT

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;

Re: How can I round time using SAS?

aminkarimid — Fri, 12 May 2017 20:48:28 GMT

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. -##

Re: How can I round time using SAS?

aminkarimid — Sat, 13 May 2017 16:03:04 GMT

Hello; Thank you for your attention;

Please help me how can I round all time.

Re: How can I round time using SAS?

Tom — Sat, 13 May 2017 18:56:00 GMT

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);

Re: How can I round time using SAS?

aminkarimid — Sun, 14 May 2017 07:28:06 GMT

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;