## How can I round time using SAS?

Solved
Regular Contributor
Posts: 166

# How can I round time using SAS?

[ Edited ]

Hello everybody

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

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.

Accepted Solutions
Solution
‎05-14-2017 03:28 AM
Regular Contributor
Posts: 166

## Re: How can I round time using SAS?

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;

All Replies
Super User
Posts: 13,563

## Re: How can I round time using SAS?

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.

Regular Contributor
Posts: 166

## Re: How can I round time using SAS?

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
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.
attachments. -##
Super User
Posts: 8,115

## Re: How can I round time using SAS?

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

SAS Super FREQ
Posts: 4,242

## Re: How can I round time using 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;``````
Regular Contributor
Posts: 166

## Re: How can I round time using SAS?

Hello; Thank you for your attention;

Solution
‎05-14-2017 03:28 AM
Regular Contributor
Posts: 166

## Re: How can I round time using SAS?

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;
☑ This topic is solved.